In defence of NULL

There was a blog post a while ago that declared that NULL was the worst ever mistake in history of computer science. The fact that nulls existed meant that code always has to be written to handle them, otherwise programmes would crash with Null Pointer Exceptions or their Windows equivalent Object Reference Not Set To An Instance Of This Object. Life would be easier and less error-prone if there was no such thing as NULL.

It may have a point when it comes to object-orientated programming languages, but when comes to databases, the suggestion that the concept of NULL is a dangerous bad idea is nonsense. The fact that some common beginners’ mistakes involve a lack of understanding over how to use NULL does not change this.

NULL has meaning.

Precisely what it means is dependent on the context, but it still has meaning. Consider an End Date. What does it mean if you have no End Date? It means, perhaps, that you are still employed, or maybe even still alive. Defaulting to some arbitrary date far, far into the future in this context is nothing but an ugly kludge. You still have to write code to handle that value, and you’ve probably got a greater potential for error than if you’d just used NULL.

NULL has meaning.

Another example. Imagine a stock control system. Each stock item has a re-order level. When the stock level reaches this point it will trigger an automated re-order process.

Now imagine there are some stock items where you want to keep track of how many you purchase and use, but you don’t want to hold them in stock; perhaps the value is high and lead time is low, and you don’t use them frequently enough to want that much capital tied up in inventory. You will only raise purchase orders manually when you know you’re going to need the thing.

So most of the time the stock level is going to be zero. But you don’t want that automated re-order process to touch it. What value do you give the re-order level?

Now, you could set it to -1, and one stock control system I know did precisely that. But that’s still a kludge, and there are scenarios that will break it.

What if the stock transactions are transmitted via a mobile network that’s not 100% reliable, which means they’re not guaranteed to be real-time and may be received out of sequence? Again, I know a system that works like this. For one thing you can’t enforce a database constraint that makes sure the stock quantity never goes negative. It’s possible that you might end up with a stock level of -1 for a short period because a receipt transaction has been delayed by an electronic sargasso. Then there’s a window in which that pesky re-order process could turn up and do mischief.

If you’d just let the re-order level be NULL for those items you only want to re-order manually, you would not have that potential bug.

This is why we have NULL.

This entry was posted in Testing & Software and tagged . Bookmark the permalink.

5 Responses to In defence of NULL

  1. PaulE says:

    So, how does the stock system deal with items that are being replaced by an alternative item (i.e. becoming obsolete)?
    I would have thought that in itself was enough to justify a re-order Yes/No flag.

  2. Tim Hall says:

    When you get into forward and backward compatabilties of alternative items, that’s a whole ‘nother can of worms. For added horror, try to build a system that can cope with the engineers and the stores managers not being able to agree over what’s compatible and what isn’t.

  3. PaulE says:

    Oh dear, if senior users disagree, projects can go pear-shaped very quickly.

    The HR system leaving date is an excellent example for null.

    A death date in a family history application opens up the possibility of Null meaning two things – “still alive” or “information not known” (and sometimes it could be either).

  4. Tim Hall says:

    Oh dear, if senior users disagree, projects can go pear-shaped very quickly.

    Can you guess what happened?

    To make matters worse, there was a management turf war going on at our end too, which hardly helped. Then we had a consultant on site who resigned mid-project and began working for the customer the next day.

  5. John P. says:

    Usually items are given a reordering policy so you can decide what sort of calculation is done in each case. That allows you to exclude items altogether and manage them manually.

    Negative stock can be perfectly feasible just because the warehouse choose to stack up their receipt postings until they have time to do them in a batch, but meanwhile the items are placed ready for picking, which menas they are consumed before being received.

    The main use of null is to tell whether a value is a positive input or simply a lack of data. For example, test results where zero is a valid input. If you just have a zero rather than a null, does that mean that the user has not entered anything yet or that they have actually entered a zero? If you use a null then you can tell the difference – a null means they haven’t entered a value yet and zero means that they actually typed zero. That way you can verify if all the results have been entered before proceeding.