tim.stevens (8/24/2010)
This is one of the best concise treatises on NULL I have seen. Your adminition about a well architected database design not allowing NULLs for any columns is as true as it is bold. The concept of NULL is, quite simply, a flawed one and really has not business being a part of the relational model (See E.F. Codd, The Relational Model For Database Management, ISBN 0-201-14192-2). That aside, having a definitive (and informed) strategy for handling these pesky buggers saves hours of hair-pulling.
You should have mentioned that EF Codd and CJ Date (and others) conducted a very long, very pointed, and very public debate concerning the role of NULL in the relational calculus. As I recall, Date had a column titled "According to Date" in one of the monthly mags (?Database Programming and Design?) from Ziff Davis or Miller Freeman (it has been a long time:-P ).
Certainly there is academic interest in various theoretical ideals such as nth Normal Form, NULL-less schemas, etc. Those of us faced with more practical problems need pragamitic solutions.
We generally accept that something approaching 3rd Normal is adequate and appropriate for most OLTP situations. Similarly, when properly defined and implemented NULL can yield a nice concise data model that is very efficient and safe. As one example, I have found it very useful in relationship tables that must represent mulitple, changing relationships over time. The "Current" department(s) for a broker is the one whose EndDate is NULL. Previous departments are represented with Start and End date values. This particular implementation permitted multiple, overlapping, and concurrent assignments.