Employee attendance table design
Hello guys,
my previous experience tells me that here in this forum are very experienced programmers and maybe some of you had more experience in this types of systems.
I'm creating an attendance app for a factory where workers will scan cards at starting a shift and ending their shift.
The database would be MySQL.
Workers are working in 3 shifts (from 6 to 14, from 14 to 22, and from 22 to 6) that rotate every week. They will punch only when they enter the shift and when they leave.
Some workers also can come to another shift and that will be over time. For example, they work from 6 to 14 and then they come again from, for example, from 18 to 20.
What would be your advice, which attendance table design would be a better choice:
1. One row for Check-in and Check-out, for example:
PunchID, EmployeeID, CheckInDateTime, CheckOutDateTime
2. One row for every Punch but workers need to select if they are coming or leaving, for example:
Code:
PunchID, EmployeeID, DateTime, CheckTypeID
(where CheckTypeID would be cIN or cOUT)
3. One row for every Punch, where I will guess in the StoredProcedure what is CheckIn and what CheckOut is, for example:
PunchID, EmployeeID, DateTime
If you have any experience in this type of system, your advice would be great!
Thanks, Davor