Best way to store multiple values from multiple daily transactions

Question

I'm currently developing a Timeclock web application which will store the date, hour, and minutes which a user logs into and out of the application via a MYSQL Database. Users can log into and out of the application multiple times per day, thus 1 user can have up to 6 transactions per day. What would be the best way to store multiple transactions such as above, in a database? I'm a web developer, and our Database Admin is currently gone for summer, so I apologize if this is a pretty easy question, but I'm confused on how to optimize the storing (and later retrieval) of multiple values in this instance.

Answer 1

I would normally expect that you'd have at least the following:

User table: has user's login_id (probably literally the ID they use to login to the system), FullName, plus other user info (don't have enough info to know what else goes in here - department, job title, etc.). This is technically optional, but is helpful to tie logins to people's actual names.

TimeEntry table: user's login_id, EntryTime (probably using the timestamp data type), EntryType ("I" for login, "O" for logout - this is optional, but helpful if it's possible for login or logout values to be missing (for instance, an involuntary logout due to a power failure)); plus, other relevant values (if any).

The login_id makes sure the entries belong to the right user. The times give us the order in which things happened. As noted, the login/logout flag helps if an entry is missing for some reason. Not sure if this is being captured automatically or if it requires action on the user's part; missing entries much more likely with the latter. I'm assuming that, when populating the EntryTime column, you'll just use CURRENT_TIMESTAMP(); should provide maximum available time data, and help line up very rapid login/logouts if EntryType can't be set. (I've captured similar data from the database end on an MS SQL Server system, and the application would usually have one login that lasted for well under a second before actually connecting for real.)

NOTE: If it's possible to have a login without a logout, EntryType will help you properly match the pairs you have. If you have 5 entries for a user on a given day (say, "8AM", "10AM", "1PM", "4PM", "5PM"), and no EntryType flag, you don't know whether the missing entry is a login or a logout, or where it falls; if that is available (["8AM", "I"]; ["10AM", "O"]; ["1PM", "O"]; ["4PM", "I"]; ["5PM", "O"]), then you can easily identify the unmatched entry, and have at least a minimum confirmed hours logged in (3 hours).

AND: remember to allow for the possibility that login and logout aren't on the same calendar day (if applicable).

Answer 2

What happens on the 7th session?

I suggest you simply store all sessions. Later, when querying the table, you still have the info that someone was "doing too much work". And/or you can just list the 'first' 6.