views:

117

answers:

6

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).

A: 

I think that if someone doesn't come in, it make the most sense to mark it as 0 minutes. Because they came in for 0 minutes that shift.

Also, because it is a numeric value, you don't really have to do anything special to the data. Which in my opinion should be the end goal of collecting data. Make everything uniform, and the later test on it. If you wanted to alert someone that this person didn't come in, just check for records where the time is equal to 0.

Chacha102
The problem, as I see it, with a numeric value, is that it requires being very specific in what the situation is. If they arrive early, the time arrived is a negative number, if they arrive late, it's positive. If they leave early, it's negative, etc. And they queries have to take which row is which into account. By making it a timestamp, it simplifies the whole situation to "when did you actually show up, when did you actually leave" and thus the data is less reflective (judgmental, in a way) of "you were late" and just reflecting the facts.
Anthony
Ah, I thought you were storing the time total the spent. In that case look for records "WHERE `Time_Started` - `Time_Ended` = 0" I believe that is a valid query.
Chacha102
But I was thinking it over a while, the idea of using a number instead. The other problem, based on your suggestion, is that I can't really say 0 for no show, because it's not capturing "how much did you work in minutes" but "how late/early were you" which, even using an integer still presents the same issue. They weren't 90 minutes late to an hour and half shift, they were gone.
Anthony
The only problem with my last comment is that if you store it with Timestamps (0000-00-00 00:00:00), you might not be able to subtract. In that case you would want to store it as a time() value
Chacha102
You can use TIME_SUB(date1, date2) to subtract timestamps, I think. But yeah, MySQL gets fussy (like Excel, and probably most computers) when it comes to the idea of subtracting time.
Anthony
A: 

How about creating a separate table (ShiftTimes?) that contains an identifier for the employee, an identifier for the shift (possibly the date but that won't work for 3rd shift), and the enter and exit times. This way if the employee calls out there simply won't be a record in the table for that day.

Mark Roddy
If I understand correctly, the way this would work is : I have Table1, it's a table of when employees are supposed to work, I have Table2, it shows when they actually worked. Instead of table2 being a record of when table1 is off, it is a table of when EVERYONE worked, regardless of being on time or late or whatever. And that way if you are absent, it doesn't show up in Table2. That could work... But it might be a bit bulky. good idea for sure.
Anthony
I'm setting this as my answer because I think the suggest best suits my goals, but overall the consensus is that NULL is most appropriate, so based on my original question, that's probably the "right answer". This one is most right for me, though.
Anthony
+1  A: 

NULL is appropriate in this case because it represents the actuality that there is no data. The employee wasn't there. What else can you put there?

You should have a status of some kind attached to the record that indicates this rather than looking for nulls however (imho).

cletus
A: 

I'd set them both to null and add a flag field specifying if they showed up at all or not.

Spencer Ruport
+1  A: 

I would put NULL because there is actually no data available (i.e. no shift start/end time to be reported).

On the report, you can skip these NULL values and do a separate processing on it. For example, you can now count how many shift an employee have missed by counting the NULL rows.

Adrian Godong
A: 

I agree that you should add a separate column to track if they were absent or not. Then it really doesn't matter if you leave the start/end times in there or not. You can simply look for the IsAbsent flag directly.

Honestly, it really depends on how fancy you want to make it. I've done a similar system and what we'd do in a situation like this is have a separate record all together logging the absence and determine if it was excused or not. Because ideally there are many other reasons other than being sick or just not showing up.

What if the absence was excused (i.e. vacation) and they should get paid? Your business rules will drive those decisions I guess.

Just try to think of all the possible scenarios before you make any final decision. You may find in the end that the start and end times become irreverent in this situation after all.

Don
If I make it a separate column, I still need to put something in the date columns. I can put NULL, but part of why it's frustrating is that If I add that extra column (or another table for just absents), the query gets more complicated. I was trying to think of a design where the data just sort of reflects the situation so that one simple query would handle it all. Having said all of that, I think going with a punch-in/punch-out system might be the best way to go. Where absents aren't recorded at all and thus doing a simple join catches where they didn't punch in.
Anthony
oh, and by the by, if they are on vacation, they are not on the schedule (good question, though!) so won't be missing a scheduled shift. This is a way of creating what I guess are called "actuals", a report of scheduled time vs actual worked hours. But it is a bit more complex, like you suggest. For example: my boss changed the schedule this week so that I worked on Tuesday, but forgot to upload the change or let me know, and for some reason no one called to yell at me either. So they don't consider it my fault, but I shouldn't get paid either. But a "missed work" ticket generally is frowned..
Anthony
upon, so this system needs to account for those situations. My plan is to have a form where managers can record lateness/overtime/absents, and they put in notes and choose a "type" (sick, late, etc). But the main table we're discussing only captures the actual data, while the notes get added to a "notes" table. that way there is a ongoing record if, for example, someone calls in sick , then shows up mid-day. The actuals simply show them as arriving 3 hours late, the notes show two entries, one for sick, one for "update".
Anthony
And if I may just go on for one more second...What is interesting is that someone who shows up when they call in sick would get some kind of "update" type note, thus making it seem like a not-so-bad thing, while a "no show" note which gets updated to a "late" note (I didn't call in, three hours later I show up), would reflect "this is bad." Ultimately we want something automated enough to make this data easy to enter and easy to follow up on, but sophisticated enough so that I'm not viewed in the same way as mr. late (when reports are run) because I had the flu
Anthony