MIDRANGE dot COM Mailing List Archive



Home » JAVA400-L » February 2008

Re: jt400 timestamp issue



fixed

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

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.

http://www.databasedesign-resource.com/null-values-in-a-database.html

http://en.wikipedia.org/wiki/Null_(SQL)


Joe





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact