What is best practice on how to handle nulls

Question

In you database and/or code.

Do you avoid nulls or do you explicitly program to handle them in your logic?

What is best practice on how to handle nulls?

Answer 1

One of the basic problems with nulls is that there is no single generally accepted meaning for their interpretation and therefore the results of using nulls are frequently inconsistent and different to the intended meaning.

Typically nulls have to be hidden or somewhat abstracted from end users. Therefore if the developer decides to put a null into a database he usually also has to determine how to remove it again later on.

For these reasons it can make sense either to avoid using nulls altogether or to minimise their use.

If you do permit nulls then you should carefully define the meaning and correct treatment of those nulls and make sure your code handles them correctly. If you don't use nulls then you don't usually need to do that - although you may want to design your code such that nulls don't get generated even where none exist in the database.

Answer 2

Nulls are both "inevitable with any existing SQL engine" and "the plague and the cholera of data management all in one".

When using SQL, the key is to remember that your SQL tables are really at the level of "physical design", where you want to keep only a single record of distinct properties p1,p2,p3 of the same object, identified by some identifier I, as a single row (I,p1,p2,p3). Even if some of those properties happen to be "unknown". (I don't mention "N/A". This particular meaning exposes design flaws.)

But that SQL level is something quite distinct from the "logical level" that your application (/user) is likely to be wanting to deal with, because that "logical level" consists of three separate kinds of assertions of fact : "I has property p1", "I has property p2", and "I has property p3". At that level, you do not want to be bothered with the fact that any of the three might be unknown.