David Gibbs wrote:
Mike Cunningham wrote:
How about making the field null capable and setting the value to null
when the employee is permanent?
Or have a field that indicates whether the employee is permanent. I
realize that in this particular case, the NULL value for the expiration
date serves the same purpose as a permanent flag, but I'd like to voice
a concern (and that's it, just a concern- your mileage may vary) about NULL.
I am going to tread very lightly here, but the use of NULL fields should
be very carefully addressed. Many of the biggest name in RDBMS design,
including Chris Date and to a lesser degree Dr. Codd himself, advise
caution with regard to NULL. In the original relation database design,
NULL simply meant "unknown". But even that over-simplistic design
required basically a new brand of logic, ternary logic in which any
condition was true, false or unknown. By allowing this "unknown"
capability you get some really screwy results, especially in aggregate
Not only that, but years later Codd himself realized that NULL had at
least two distinct meanings: unknown but applicable (meaning we didn't
yet have the information - a field wasn't entered) and unknown but
inapplicable, which is the closest to the meaning David is ascribing
here, where the NULL value means the employee expiration date is
inapplicable and thus (by extension) this is a permanent employee.
It's the "by extension" that gets tricky. There are a lot of reasons
why a value isn't known. When entering data from written forms, it
could be anything from "they didn't enter it" to "I couldn't read what
they entered" to "the data entry clerk forgot to enter it". And that's
true for any field. It gets more complicated: let's take "race" on a
personnel record. In addition to the things I enetered above, NULL
value there could mean "I refuse to answer" or "multi-racial" or even
"I'm not sure and I don't care".
All I'm saying is that NULL should not be overused, and if it is going
to be used for anything other than the original "data missing" concept,
then you need to be very careful.