Database Design – Model a Company with Owner, Partner, Employee

One thing you might want to consider changing is making clocking.clock_out_date nullable. Until the employee clocks out there isn't a reasonable value to use here, so NULL is the best choice.

You can tell the difference between someone who is still working (theoretically) and someone who forgot to clock out by checking their previous clocking record when they clock in. If the previous record has a NULL clock_out_date then they obviously forgot to clock out.

You could also create a report that checks for employees who haven't clocked out for more than (some configurable number of hours) since they last clocked in. You should be able to get a reasonable number for the longest expected shift. It may be once in a while that you report an employee to management for not clocking out when they are actually still working, but that should be rare and the manager will know it - or find it out quickly enough, so the few false positives you might generate aren't serious.

One other thing you might want to think about is not keeping the hourly rate right on your staff table. It might be better to keep this in a child table which includes date ranges since you want to be able to let a wage go up or down over time and it would be good to (a) have a history and (b) be able to enter future-dated changes to relieve time pressure for data maintenance.

Without knowing the business rules it is generally hard to judge if a model is valid or not. However, here are some quick points:

1-Address relationship to employee should be reversed. An employee could have several addresses. Your address modeling may be valid if you are referring here to work address where multiple employees work at the same location. Similarly, there is an issue with the phone to employee relationship.

2-Self reference relationship in Employee must not be mandatory on the many side.

3-Relationship between employee and leave balance is awkward. An employee should have 1 leave balance per year but your columns don't show that clearly.

4-Phone table, should separate area code from actual number for land-line phone numbers.

5-Naming: DOB Date is a bad name. DOB means Date of Birth, so the word date is redundant.

6-Salary is not related to Promotion from a business sense as you have it. Salary is related to grade, level, etc.

7-Some relationships are mandatory in the model where they should not be, I have already pointed out the self-referencing one but you also have the Employee-Dependant relationship. It should not be mandatory. The same is true for leave taken.

From a professional stand point, I think you should not use this mode for a production application as is or even with the remarks I mentioned here because I did not provide a full list. Make sure you consult with a more experienced professional before implementing such a model in production.