Should I allow null values in a db schema?

Question

I know that logically, there are some cases where NULL values make sense in a DB schema, for example if some values plain haven't been specified. That said, working around DBNull in code tends to be a royal pain. For example, if I'm rendering a view, and I want to see a string, I would expect no value to be a blank string, not "Null", and I hate having to code around that scenario.

Additionally, it makes querying easier. Admittedly, you can do "foo is not null" very easily, but for junior SQL devs, it's counter intuitive to not be able to use "foo != null" (and yes, I know about options to turn off ANSI nulls, etc, but that's definitely NOT simpler, and I don't like working away from the standard).

What good reason is there for having/allowing nulls in a database schema?

Answer 1

The most significant reason for allowing NULLS is that there is no reasonable alternative. Logically, a NULL value represents "undefined". For lack of NULLS, you'll end up trying to specify a "dummy" value wherever the result is undefined, and then you'll have to account for said "dummy" value in ALL of your application logic.

I wrote a blog article on the reasons for including NULL values in your database. You can find it here. In short, I DO believe that NULL values are an integral part of database design, and should be used where appropriate.

Answer 2

C.J. Date in his book "SQL and Relational Theory" (2009: O'Reilly; ISBN 978-0-596-52306-0) takes a very strong stand against NULLs. He demonstrates that the presence of NULLs in SQL gives wrong answers to certain queries. (The argument does not apply to the relational model itself because the relational model does not allow NULLs.)

I'll try to summarize his example in words. He presents a table S with attributes SNO (Supplier Number) and City (City where supplier is located) and one row: (S1, London). Also a table P with attributes PNO (Part Number) and City (City where part is produced) and one row: (P1, NULL). Now he does the query "Get (SNO,PNO) pairs where either the supplier and part cities are different or the part city isn't Paris (or both)."

In the real world, P1 is produced in a city that either is or is not Paris, so the query should return (S1, P1) because the part city either is Paris or is not Paris. (The mere presence of P1 in table P means that the part has a city associated with it, even if unknown.) If it is Paris, then supplier and part cities are different. If it is not Paris, then the part city is not Paris. However, by the rules of three-valued logic, ('London' <> NULL) evaluates to UNKNOWN, (NULL <> 'Paris') evaluates to UNKNOWN, and UNKNOWN OR UNKNOWN reduces to UNKNOWN, which is not TRUE (and not FALSE either), and so the row isn't returned. The result of the query "SELECT S.SNO, P.PNO FROM S, P WHERE S.CITY <> P.CITY OR P.CITY <> 'Paris'" is an empty table, which is the wrong answer.

I'm not an expert and not currently equipped to take the pro or con here. I do consider C.J. Date to be one of the foremost authorities on relational theory.

P.S. It is also true that you can use SQL as something other than a relational database. It can do many things.