When Null Values Should be Used

First of all, let’s get this straight: there are appropriate times when using null database fields. Those times are “rarely”, “seldom” and “never”.

Take this gem that I recently came across in a SQL database on a table dealing with work orders:

Completed (bit, null)

Why the heck is a completed field made nullable? I have no idea. Whoever the original developer was probably didn’t think things through properly, or perhaps had some obscure reason for doing it.

Try thinking about this in a different way: When is the status of a work order not known? Either it is completed or it is not completed. There really isn’t much in the way of middle ground on such things. Having a null status really seems about the same as saying “I don’t know!”. If you don’t know, chances are it hasn’t been completed.

I can picture a conversation between workers:

Daryl: Hey Jim-Bob, has anyone done that there task yet?
Jim-Bob: I dunno, lemme ask Cletus. Hey Cletus, you bin’ done doing that task?
Cletus: … [No response – Cletus is sleeping at his desk]…
Jim-Bob: Cletus ain’t awake. I don’t think he done it yet.
Daryl: Oh, OK.

Even in such as scenario, someone knows whether the work order has been completed or not. If nobody knows, someone could go and check.

Far too often I come across things like this: database columns marked as nullable when they really have no good reason being nullable. Nullable values themselves don’t generally cause problems – just everything around them. When dealing with nullable objects you have more checking that you need to do to safely handle values. Not only do you need more code to safely handle nullable objects, you have to do more testing. What you really get in the end is more headaches.

There are appropriate times to use nullable fields in databases. For example, that same work order table could have a field for a user Id who has completed the work order. If no one has completed it, then the user Id would be null. It’s like saying “Who has completed this work order? Oh, no one? Oh, OK.” The lack of a user Id indicates “no one” – putting any sort of value in there would indicate that someone has completed the work order, which isn’t necessarily the case.

Again, there is a time and a place for using nullable fields.

Please, for the love of code, think things through before making your fields nullable.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s