Tracking hours with clock in/out

Question

I am working on a web interface that would allow the user to keep track of the amount of time people spend at their establishment.

The problem:

I don't know what the best way to keep track of this time would be. I have come up with a couple ideas on how this could be accomplished, but none of them are as elegant as I would like them to be. I could:

1. Store clocks in a MySQL database and calculate hours when requested.
2. Store clocks and hours which would be calculated every time they clock out.

Why these solutions aren't good enough:

1. Calculating the hours per request would rely heavily on a person clocking in and out in sequence in order to get this done elegantly. This would offer no flexibility when it comes to people forgetting to clock out. Maybe improper clock order could be flagged as a discrepancy and it won't calculate hours until it is resolved? It also seems like the query to get sequential clock ins/outs and determine the difference between them would be cumbersome.
2. Calculating hours every time a person clocks out seems a little more reasonable, but then you would have to account for people forgetting to clock out. If someone forgets to clock out for a month, they would come back with a bunch of hours and the user would have inaccurate records.

I ask because I may be looking at this problem too closely and am failing to see an obvious solution. Any suggestions?

Answer 1

Your database should be concerned with the facts. The interpretation of the facts does not need to be stored in the database, because it can be calculated on demand, each time you need to display the number of hours between clock-in and clock-out events.

Sure, it might be a bit harder, but trust me, you will have to come up with the algorithm anyway, because at some point the need will arise to recalculate everyone's hours. So, since you will come up with the algorithm, you might as well keep using it each time you need to display hours in the application, instead of each time you perform a clock-out on the database.

Discrepancies in the data due to a person forgetting to clock out is something that you need to handle, either when it is time to show the number of hours, or, better yet, by means of a batch, recurring process which looks for discrepancies and fixes them by inserting extra events to the database. So, for example, I would run such a process at midnight every day to clock out everyone who apparently forgot to clock out. (I would also keep track of the fact that this clock-out was system-issued and not user-issued.)

As a bonus, here is a query for Microsoft Access (in MS-SQL Server compatibility mode) that I wrote a long time ago to calculate employee shifts, given clock in and clock out events. Actions.TypeId=2 means we have a clocked state change, (the system supports other types of events too,) and when that happens, then Actions.Data='0' for a clock-out, while Actions.Data='1' for a clock-in.

SELECT 
    Employees.Id, 
    PunchIn,
    (SELECT TOP 1 DateTime FROM Actions 
      WHERE EmployeeId = Employees.Id AND TypeId=2 AND Data='0' 
        AND DateTime > PunchIn ORDER BY Id) 
    AS PunchOut, 
    (PunchOut-PunchIn) AS Duration
FROM Employees LEFT JOIN 
    (SELECT Actions.EmployeeId, Actions.DateTime AS PunchIn
       FROM Actions 
      WHERE Actions.TypeId=2 AND Actions.Data='1') 
    AS PunchIns 
    ON Employees.Id=PunchIns.EmployeeId
ORDER BY Employees.Id, PunchIn;