George H. (6/30/2011)
Hello,I found this article interesting. However, granted I'm not a full-time DB designer, I am slightly confused by the statement:
Some people would still use the three columns, using a silly number like 000 000 0000 to indicate "no such number". That's better than using NULL to indicate "no such number".For existing databases (in cases where I can't design the table from the ground up) I prefer not to use some arbitrary value to denote "no value" and do use NULL values in this case. I find using LEFT JOINs or searches WHERE x IS NULL easy enough in these cases...maybe I'm missing something?
However, I do agree that it's always best to extract the columns out into their own table - removes the problem completely.
Is there an article/source that explains why it is better to use a predefined value instead of NULL?
Thanks, George
.NET Programmer Analyst
Well, NULL should always mean "The database doesn't contain this value", so it would be used in the case where the value is applicable but unknown for one reason or another as well as in the case where there is no value because it's not applicable; if you want to be able to tell for certain that the value is inapplicable, you can't use NULL unless you can guarantee that there are no cases where the value is applicable but unknown. That's my only reason for preferringto avoid using NULL for this particular purpose.
Other people have other reasons - C.J.Date for example is quite famous for his anti-NULL stance (as well as for many far more important things) so you might find something in one of his books or papers, and both David and Steven who have commented above have a much stronger anti-null attitude than mine so perhaps they could point you towards something suitable for you to read.
Tom