2

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

  • 1
    I can see what you're doing with the 2nd table, but why do you need a 2nd table to accomplish it? In other words, aside from collecting raw data, what do you really need to do with it? – Dan Pichelman Aug 1 '15 at 17:25
  • @DanPichelman my plan was to put only the valid punch in & out in the second table and in the first to keep the history if the employee wants to argue or to investigate any case for example – Hadi Aug 1 '15 at 17:27
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.

1
  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

  • Denormalization is sometimes an acceptable compromise, but one has to understand the purpose of it, the cost of it, and be sure that the benefit is greater than the cost. – 9000 Mar 15 '18 at 18:28
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.