I am working on time attendance module and I managed to read the data from the timing machine in the following format:
Id CheckIn Type Status 0000142 5/15/2015 6:00 PM 2 OK 0000142 5/15/2015 2:00 PM 1 OK 0000142 5/15/2015 6:00 AM 1 OK 0000142 5/15/2015 3:00 PM 2 OK
where Type [
1 - stamped in , 2 - stamped out ]
I should design a table to store these entries taking into consideration the following
- Each employee should punch in first and then punch out
- Some employees have 2 shifts per day ex: 09 AM - 01 PM then 05 PM - 09 PM
- Some employees have night duties ex: 09 PM - 09 AM ( duty starts yesterday night)
what I am planning and need your kind advise is the following:
- To create table
- To create another table
FirstOutis In and Out for first shift and
SecondOutare In and Out for the second shift.
- Am I breaking the normalization by adding the second table?
- The second table will contain many NULL values, is this a bad designing?
- How to eliminate the multiple entries for example, some employees will sign in twice and sign out 3 times so how to remove the duplication in entries but with different timing?
- Last question which is the nightmare of me, the schedules that starts tonight and ends tomorrow morning, how to control them since i cannot control them by the CheckIn datepart
If you need more explanation, I am ready. Than you in advance