Here is a model I was planning on using to store employee data for :
Clock in and out for day.
Clock in and out for Breaks and lunch.
And in future be able to track time clocked off jobs for other reasons throughout day.
Been a bit since I have done any of this…
Do you see any problems?
I don’t see any problems, this table structure looks good. You will know when they clock in and out so you can find the number of minutes they have been working where you can subtract out all of the breaks. You setup it nicely for joins.
Looks functional to me ... maybe merge tbl_breaks into tbl_timeclock?
As a simple example, you'd have the following break codes ...
[quote]ID: 1, Description: Working
ID: 2, Description: 30 Minute Lunch
ID: 3, Description: 15 Minute Break[/quote]
... when an employee clocks in for the day, you'd insert a record into tbl_timeclock ...
... when an employee takes his first 15 minute break, you'd update the last record and insert a new one ...
[quote]ID: 1, EmployeeID: 1, BreakID: 1, ClockIn: 05/06/2010 08:00:00, ClockOut: 05/06/2010 10:00:00
ID: 2, EmployeeID: 1, BreakID: 3, ClockIn: 05/06/2010 10:00:00, ClockOut: NULL[/quote]
... when they finish their break and go back to work, update the last record and insert a new one ...
[quote]ID: 1, EmployeeID: 1, BreakID: 1, ClockIn: 05/06/2010 08:00:00, ClockOut: 05/06/2010 10:00:00
ID: 2, EmployeeID: 1, BreakID: 3, ClockIn: 05/06/2010 10:00:00, ClockOut: 05/06/2010 10:15:00
ID: 3, EmployeeID: 1, BreakID: 1, ClockIn: 05/06/2010 10:15:00, ClockOut: NULL[/quote]
... time for lunch!
[quote]ID: 1, EmployeeID: 1, BreakID: 1, ClockIn: 05/06/2010 08:00:00, ClockOut: 05/06/2010 10:00:00
ID: 2, EmployeeID: 1, BreakID: 3, ClockIn: 05/06/2010 10:00:00, ClockOut: 05/06/2010 10:15:00
ID: 3, EmployeeID: 1, BreakID: 1, ClockIn: 05/06/2010 10:15:00, ClockOut: 05/06/2010 12:00:00
ID: 4, EmployeeID: 1, BreakID: 2, ClockIn: 05/06/2010 12:00:00, ClockOut: NULL[/quote]
... and so on. The solution's got its pros (simpler design) and cons (lots of records) ... not saying it's a better approach, just offering an alternative.
A quick Google led me to a blog post someone made which contained a database diagram for their "Time Tracker Database" ... http://raincitysoftware.com/docs/TimeTracker.png. Also, I read a nice blog entry a while back which proposed breaking the date and time into two different columns ... might be something to consider.
Good luck!
I actually think the original design is better from a query standpoint, although I would make one change, I would make two breaks tables – paid_breaks and unpaid_breaks. This would make queries easier in that hours_paid = timeclock_hours – unpaid_breaks. If you keep paid and unpaid breaks in the same table you’ll need to track which is which and sort through those records to determine which breaks should be subtracted from the timeclock_hours. Since paid_breaks aren’t used in the calculation of hours_paid, why bother asking the database to search through those records.
What about adding “Payrate” into tbl_breakcodes
Second thought, won’t work there. Two employee’s could have different rates for the same break.
