NULL dates - best practices?
-
When designing tables I usually don't allow null values. Numeric and bit values default to 0 and string values to ''. To me this indicates that the value is blank, not unknown. My problem is with dates. Since there is no such thing as a 'blank' date field, I have to allow nulls on date columns. Using some arbitrary default (01/01/1900) is unacceptable since this could be a valid value. How do others handle default dates?
TIA
John Deupree
-
Well, depending on the nature of the field is 1/1/1900 a valid date? In the past I have used my birthday with HH:MM, knowing that for what we were storing (change dates, edit dates) my birthday was too far in the past for it to be valid.
Henry
-
That might be OK depending on the field. It wouldn't work for birthdays (in our application - at least for a whiile) for instance.
John Deupree
-
You could go with a fixed date way in the future but that would also have some issues.
-
What exactly is wrong with having a NULL date if the date is actually unknown or does not apply?
It is a good design practice to not allow a required column to be null, but if you have conditions where it is unknown or does not apply, it does not seem like a good practice to require a fake date.
-
It's not that it doesn't apply. For instance, if I enter a customer record, but don't know the birthdate, I leave it blank. It has to be null or there is incorrect data there. It might be filled out later. I guess I'm agreeing with you, but just wondered how others handled that.
Thanks
John Deupree
-
In this particular case then, the birthday is indeed unknown - and 'should' be represented as NULL.
Especially since you also say that it may be filled in at a later time, which in itself says that this particular column may be NULL, since data can be entered even though birthday is known or unknown.
The only token we have for 'unknown' is NULL.
If you go the other route and use a dummy value, then it's no longer unknown in the strict sense, since you have data there. Be it something like 1800-01-01, it's still a valid date - not unknown - then it depends on other rules that would translate 1800-01-01 to unknow - in the end the same thing..
Allwoing nulls or not is a design choice.
Personally I do use them where it makes sense.
I don't believe nulls or no nulls is a black or white issue. There is a gray zone in between.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply