SO I'm working on a schedule app that uses a MySQL DB. One of the modules for this site is for managers to track tardiness, overtime, and absents. The current site is a mess. It just has the manager enter in the key data, but it doesn't bind the manager to any of the already known data (like when the employee was expected to arrive/leave) or even require them to use a standard format. Some managers enter 30 minutes (meaning 30 minutes late), while others might enter 8:30 (meaning they were due in at 8:00), etc, etc.
My version will be much more limiting. The manager will choose the employee, then choose the shift in question, and they basically will be able to enter two values: time actually arrived, time actually left. This way, when reports are run and timesheets are verified, it is simply a matter of doing the math to see if they were late/early/overtime. But I've run into a tough spot:
First, if someone calls in sick (or just doesn't show up), I will have it so the manager just checks a box for that, and they don't make any time adjustments at all. It just blacks out that time altogether. Should I do this by setting both time values to NULL, and when any queries are run, NULL will be dealt with as need be (perhaps reversing the time left and time arrives so it comes out to zero on both ends), OR should I have both time arrived and time left both set to the "expected shift end" time, so that it works out to zero?
I don't like using NULL because it just feels like there is no data available, but I don't like using the expected shift end because it makes it seem as though they really did arrive, just right as they were also leaving.
This is really a question of how to make the data semantic without having to make it overly complicated (with a NULL).