NULLs are a perennial problem. Nobody likes them. They confuse developers and users and many analysts do not really understand them.
The concept of NULL allows us to say that there are things that we do not know.
In his article on SQL Smells, Phil Factor associates several smells with NULLs. In this post I’ll explain how to avoid using NULLs and how to use them properly when they are necessary.
Why do we need NULLs at all? What is the benefit and what is the cost?
NULL has a simple meaning with wide-ranging and surprising consequences. NULL means the value is unknown. And this in turn means that the result of any calculation or concatenation which uses this value must also be unknown!
NULL is a feature of SQL. The benefit of allowing NULL or “three valued logic” (TRUE/FALSE/UNKNOWN) is that it allows a database to record that there are things we do not know. The cost of having them is that any calculation or concatenation which uses this value must also be unknown! This confuses many people.
Reasons for needing NULL
There are many reasons why we might not have data to put into a column. Thinking about why we are considering defining a column as NULLable will encourage us to consider alternatives.
The structural NULL – Permanent sub-types
Sometimes we want to combine two entities into a single “super-type” table. There are attributes of Person will never be used for Business (and vice-versa). These missing values will need NULLs.
The structural NULL – Lifecycle subtypes
Something similar can happen if we combine all the steps of a entities lifecycle into a single table. The attributes of the later stages will always be empty (NULL) until that stage is reached. These later steps often contain dates or times.
In both cases involving sub-types it may be possible to splitting the sub-types into separate tables. Consider whether it is worth the effort and make sure you avoid the pitfalls of sub-types in SQL.
Data that will never be there
Entities like “Address” are frequently modelled with attributes like “AddressLine_”. In many cases there will never be values for the later lines. They will not be mandatory in the user interface, but do they need to be NULL? Consider whether allowing them to default to “spaces” or an empty string, would be better and whether it would have any bad effects.
Things which should never allow NULL
Always decide whether you expect an attribute to have a value. Don’t leave it to chance.
There are some things which should hardly ever allow NULL. This includes all keys and identifiers. Avoid allowing short titles or descriptions to be NULL (For long descriptions allowing NULLs is understandable).
There are some attributes where allowing NULL is hard to avoid. Life insurance and pensions companies may need a “date of death” for their customers! Having a column with allows NULL is often the easiest way of handling this.
You should resist the temptation to use “magic dates” or inappropriate data-types in order to avoid allowing NULL. The consequences are far worse than the problem.
NULLs are a problem, nobody likes them but they are necessary. Many problems with NULLs can be avoided by two rules:
- Remember that NULL means “unknown value” and this has consequences.
- Ask “_why_ don’t we have this data?”
In many cases NULLs can be avoided by data modelling – that means the analyst has to do work in the Conceptual or Logical Model.
The next article is about another smell: having the same name for different things!