NULL values are something that a lot of people just don’t understand the use off.
I often come across databases when instead of using a NULL value some known static value is put in instead. For dates its usually 1/1/1900, for numbers -1 is quite popular, for character fields a blank value is great.
The biggest excuse for not using NULL values in a database that I see is that the client programmer doesn’t want to have to write an IF statement around the database column in case it is NULL, so the use some other column. What happens later on, they write some code around the default value so that it doesn’t display if that value is in there because the business unit doesn’t want to see 1/1/1900 for a deal close date in the purchasing system if the purchase hasn’t closed. So instead of changing the database value to NULL the database is left with these useless meaningless values in it.
NULL means unknown. Not knowing the value is many cases is perfectly acceptable, and should be treated as such. If not knowing the value isn’t acceptable, then don’t accept the record without the values.
Now, the trick with NULL is that is also means nothing (it can really mean pretty much what ever you want, but I’m sticking to the more basic thinking here) as well as unknown. For a lot of programmers this is a tough concept to grasp. They always want to know what all the values are, and unknown just doesn’t fit into their idea of knowing all the values, so they try and stick an value in there.
I do my best to work with programmers to get those values corrected, and get the systems designed so that they have the correct values in the tables and so that the values are being shown correctly.
The database gives us the NULL value, we should use it when it is the correct value to use.