Time attendance module design

Question

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

1. Each employee should punch in first and then punch out
2. Some employees have 2 shifts per day ex: 09 AM - 01 PM then 05 PM - 09 PM
3. 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:

1. To create table EmployeeCheckIn[ Id,CheckIn,Type,Status]
2. To create another table ManipulatedEmployeeCheckIn[Id,FirstIn,FirstOut,SecondIn,SecondOut] where FirstIn and FirstOut is In and Out for first shift and SecondIn and SecondOut are In and Out for the second shift.

my questions

1. Am I breaking the normalization by adding the second table?
2. The second table will contain many NULL values, is this a bad designing?
3. 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?
4. 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

Answer 1

1. Yes if ManipulatedEmployeeCheckIn[Id,FirstIn,FirstOut,SecondIn,SecondOut] contains redundant data which is also in EmployeeCheckIn[ Id,CheckIn,Type,Status]
2. Yes in general NULL values should be avoided if possible
3. This depends on your intention which seems to be contradicting your question. If you want to use EmployeeCheckIn[ Id,CheckIn,Type,Status] as a history as you wrote in your comment, then you should keep them in as these events occured and need to be logged.
4. If I correctly understood, you simply need to search for the employee id and search for the next entry with this id. Then you can check the CheckIn and Type part to see if the entry is valid.

I would have the following suggestions to avoid the null data. Just use one table for logging the check-ins EmployeeCheckIn[ Id,CheckIn,Type,Status,Valid] where Valid marks if the entry satisfies your rules. So once you have another entry going into the table you check if everything is fine and set Valid. Then you can easily operate on the table by filtering all that have Valid=0 (if 0 means invalid) and you have the data which you originally wanted to put into ManipulatedEmployeeCheckIn[Id,FirstIn,FirstOut,SecondIn,SecondOut]

I would do the validation in a Trigger which is activated when values are inserted.

Answer 2

1. I dont think it is a problem. Denormalization sometimes is needed for practical reason. For example, you only have two Ins and Outs, if this grows, then you should create another table such as InOuts table

2. I dont think NULL is a bad thing, for your design I dont think it is a concern

3. You need to create a new table such as InOut tables. EmployeeCheckin and InOut would have many to many relationship

4. You can use datetime and need some date manipulation to make sure that the shift is until the next day

Hope this helps

thanks,

Johan

Answer 3

I don't get why ManipulatedEmployeeCheckIn is even required; it looks like two records in the first table would adequately describe two successive checkin / checkout pairs of events.

Also consider unsuccessful checkin attempts (some Status = "Access Denied"). You need to register several of them in a short time if you want to register them at all. A reader error, or auth not prepared for a new employee. Another attempt, a call to the security dept, "OK, we fixed the problem, please try again", another attempt, success. How do you register that?

In general, I'd go with the simplest, most logical, most compact schema with least (or zero) provisions for current special cases. I'd just register events, and whatever grouping of these events may there be, belong to application / reporting layer.