Database design -- NULL or a max value to indicate this date value?

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 2009-07-25 00:10:49

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 2009-07-25 00:17:14

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 2009-07-25 00:18:48

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 2009-07-25 00:19:17

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 2009-07-25 00:21:36

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 2009-07-25 00:25:14

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 2009-07-25 00:13:09

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 2009-07-25 00:21:54

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 2009-07-25 01:14:41

+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 2009-07-25 00:15:02

A:

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

Spencer Ruport 2009-07-25 00:15:09

+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 2009-07-25 00:15:29

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 2009-07-25 00:45:41

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 2009-07-25 01:00:16

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 2009-07-25 01:04:03

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 2009-07-25 01:07:51

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 2009-07-25 01:11:49

ansaurus

tags:

views:

answers:

Database design -- NULL or a max value to indicate this date value?

related questions