Storing attendance records in the database.

Question

I found a similar question asked previously (School attendance database)

I have to deal with these additional conditions.

1. Total number of users recording attendance would be 100,000.
2. Each user will have swipe-in swipe-out entry.
3. A user may do multiple swipe-in swipe-out incase s/he is not sure data was captured.
4. A record of 1 year attendance has to be maintained which can be access by the user.

The basic table i thought was with following entries.

1. UserID - numeric value
2. Date
3. Swipe in time
4. Swipe out-time.

If this is the table then approx number of rows in database would be = 100,000 x 250(working days in yr) = 25,000,000 in ideal situation. Now if user duplicate either swipe-in or swipe-out rows will add up. Say 1/3 of employee do this to ensure attendance is marked. so additional rows 8,333,333 totalling to 33,333,333 approx.

One of the issues would be when a user swipes-in twice but swipes out only once. Then i need to have null value in the second swipe-in or fill the same value in the swipe-out field. This would add up the additional rows mentioned. The other option i thought was to run a background task every day to clean the double user entry. Say user swipes in at 8.00 A.M and then 8.10 A.M so the system removes the 8.10 A.M entry at the end of the day.First in last out time basis.

However, i prob i forsee is. If say user stays overnight in office working and swipes maybe 2.00 A.M. The swipe data would be

1. Swipe in - 1-Jan-10 - 8.00 A.M.
2. Swipe out - 2-Jan-10 - 2.00 A.M.
3. Swipe in - 2 Jan-10 - 1.00 P.M. (he comes back to office again same day - work pressure :))
4. Swipe out - 2 Jan-10 - 10.00 P.M. How to handle this?

My questions are: 1. Is the number of rows listed acceptable to databases like mysql, postgresql without delaying too much of retrival time? I would be interested more in opensource db performance. 2. Is there a better way to format the table than this?

Answer 1

Here's a little normalization:

UserTable: UserID FirstName LastName Email WhateverOtherFields UserCreated [datetime] LastActivityDateTime [datetime]

AttendanceTable: AttendanceID UserID EventID SwipeIn [datetime] SwipeOut [datetime]

EventTable: EventID EventName EventLocation EventStart [datetime] EventEnd [datetime]

With a layout like this you can keep multiple attendances on file even for the same day. You would allow user to SwipeIn to begin an Attendance per-se, and would keep that attendance open until user SwipeOut. Maybe also give the system a flushing process to allow you to close out those attendees that never got to SwipeOut. By adding something like an events table to attach to the attendance table you would allow for tracking of events and the like. You can totally go all out or KISS.

Hope this helps!

Answer 2

I don't see any issues with the no. of rows. Many applications commonly have such amount of data. For your questions below is my opinion:

1) You need to consider official working hours say 9AM to 6PM i.e 9 hrs daily. If a user overstays after midnight the remaining amount of time after midnight should be added to next day's attendance. Swipe in - 1-Jan-10 - 8.00 A.M. + Swipe out - 2-Jan-10 - 2.00 A.M. = 16hrs in 1 Jan-10 + 2hrs in 2 Jan-10 Swipe in - 2 Jan-10 - 1.00 P.M. + Swipe out - 2 Jan-10 - 10.00 P.M. = 9hrs in 2 Jan-10

So your total is 16hrs in 1 Jan-10 and 11 hrs in 2 Jan-10.

One more thing you can add in your table is column "Hours logged". Not very useful but sometimes helpful in pulling the report. You can add value to this column only for swipe out entries which will not be cleaned out i.e last out entry.

Answer 3

The simple answer is that you log swipes rather than days and then post-process the data to achieve the required tracking - even without your example there are the more more basic cases of "going out for lunch" or other reasons to go off site that require more than one arrival and departure per day.

Whatever you do you're going to have issues with multiple swipes - people being "people" you're going to struggle with edge cases i.e. where a user behaves in an odd fashion for whatever reason (usually quite innocent...).

Answer 4

Sounds like a good case for a trigger -- you're not inserting multiple rows at once (which is the normal problem with triggers that try to do things), so just don't allow a SwipeIn if the previous action for this person was a SwipeIn likewise for SwipeOut.

Answer 5

hi i think about below steps 1:make a Logical setting like if the deferent between two log in is more than the daily working our then the application can understand that the user forget to logout 2 :add a flag in the corrupted recede (which have login without logout or multi same action in same day or ....) which you got from the card reader and mack sub form to modify it manually by the attendance manager or the person in-charge 3: table design attendance table a) attId int incr empid [login] (datetime) [LogOut] (datetime) [is Complete] (bit) <-- this flag Depends on the logical setting you make and show it to the manager to modify it '----------------------- i think there is way to mange this case automatically by the system the human touch must help sorry coz of the bad english i have but i hope this will help

Answer 6

If you need an attendance recording system I recommend using ghg.com I use them for my company for my employees and when we have festivals to keep track of people, supplies, etc.