Rakesh SINGH
asked on
Need database for storing shift roster and at end of month can check employee attendance?
Shift are of four types.: A,B,C,G
After 6 days Off.
How to make this database so that at the end of the month we check there attendance. Shift changes aftet every 2 days.
After 6 days Off.
How to make this database so that at the end of the month we check there attendance. Shift changes aftet every 2 days.
Here is the gist of Patrick's solution with the changes I said above:
tblEmployees
----------------------------
EmplID (PK)
EmplFName
EmpLName
...
tblShifts
----------------------------
ShiftID (PK)
ShiftCode
ShiftDate
Where ShiftCode is A, B, C ... and appropriate calendar date. Again if you can have more than one shift on a day, it probably is better to just have this table be a list of dates and whether or not they are valid work days.tblAttendance
----------------------------
AttendID (PK)
ShiftID (FK)
EmplID (FK)
If ShiftCode not in the dates table, you can add it here. If an employee can be in more than one shift a day, then add ShiftCode to the unique index with ShiftID and EmplID so it will let you have duplicates of the employee for a given shift as long as they clock into a different shift code. I hope that makes sense.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
*In many systems, there is a date table with a flag that says valid work day (or the inverse flags weekends/holidays as non-work days). In your case, you can have this be a column with shift codes A, B, C, G by specific date if only one shift is valid for specific date. Then the attendance table just uses the PK of the dates table (e.g., DateID) and the PK of the employees table (e.g., EmplID) as a combo unique key.