Boolean types - To null or not to null?

Boolean types are quite common to be found within a database. Alike many other types there are boolean and nullable boolean types. Simple boolean types consist of two values: True and False. Nullable ones offer null as a third choice. So, do we really need this third option in our database or source code and if so how should we deal with it?

 

Nullable bool is not the only type that accepts null values. For example we got nullable integers and nullable DateTimes. So, what is the meaning of null? To put it simply null means we have no idea what the value is.  So if, you got a nullable int, its values could be 10, 100, 0 or null. Now, 10 and 100 are standard values. So is 0, even if it actually refers to the number of zero objects. However null tells us that we do not know if the value is any one of these. Supposing int refers to the Bahamas' number of citizens. Setting null value to that variable does not imply that the Bahamas do not exist or have no citizens or that there is no record of that number somewhere out there. What it means is that at that given moment we do not know what the number is and this is quite different from using 0 as that would tell that there are no citizens at all.

 

The same thing goes for the DateTime. Using null as a DateTime value is like saying that we do not know when something happened. A person who is not really fond of history classes might respond "I have no idea at all" when asked when the French Revolution took place while another person might answer that it took place at 1789.

 

The same thing goes for boolean types. When asked whether it rained all day yesterday or not you could answer yes or no, but you could also answer "I don't know" (null) if you were all day long inside your no-windows office trying to finish that project that had to be delivered no matter what.

 

By now you may be wondering why I'm writing this article concerning nullable boolean types and not nullable types in general. The reason is, common types such as integers and DateTimes I mentioned earlier can get a lot of different values. However a boolean type has only two values and by adding an extra one we move on from binary to three-value logic. So even though all nullable types share common concepts, this case makes things a little more different.

 

Imagine you create a table named Orders. This table may contain two columns: DateCreated and DateDelivered. When creating an order row you know what the DateCreated is (the date the Order is created) but you have no knowledge of when the order is going to be be delivered. Keep in mind DateDelivered does not represent the date the order is expected to be delivered, but the date the order was actually delivered. As a result this column should be addressed as nullable.

 

Now, let's see how boolean types fit in. Suppose the table requires one more column called IsPriorityOrder and by default an order is not set as having priority unless assigned to. Should that be a nullable boolean (actually the exact SQL server type name is bit) or just a plain boolean? The question that needs to be answered is "Could there be a case where we will not know if IsPriorityOrder is either true or false?" . If the answer is true, go for the nullable, otherwise go for the boolean.

 

In the case of IsPriorityOrder, if all orders by default get false value, which can then be altered to true if needed, then the answer is boolean. This case does not prove to be much difficult. However, suppose in the same scenario, that there is no way to know what the default value should be (there are as many priority as non-priority orders) and that after a person creates an order, there is some other person who gets all orders that have no IsPriorityOrder value in order to add one to them. How would you get all such orders, if both these and the ones that have verified false value look the same? Of course, using a nullable type is not the only way to move on. I am just trying to point out that in order to choose between nullable and not nullable bools you need to pay extra attention to where you want to get (as goes on with everything concerning database actually).

 

Let's do one more column. We add a column called HasPositiveReview. In this case using a simple bool type would make thinks complicated. Sure you can use it if you want, and say for example that all records get by default HasPositiveReview false value. Now when the order is completed and we get the review we can set the value to true if we want to. However, imagine you are asked for a review mentioning how many orders contain false reviews. Now you have to find all orders having false HasPositiveReview and add some extra check to avoid the ones that have not yet been set (check if DateDelivered is null for example) while in the case of nullable bool all you had to do was check the HasPositiveReview value for false.

 

Personally, I prefer solutions that make things clearer. The ones that wil make things easier for some other person (or yourself after a while) to comprehend how things work. However, since there are people who prefer to try out custom solutions, you can always choose the method you want, just make sure that it won't cause you extra trouble in future.

 

So the thing is, if you do need an extra third case, apart from true or false, use nullable boolean. If not, don't use it just in case there might be a time in future when you will need a nullable boolean, as nullable types are more complex than non-nullable ones and might cause trouble.

 

For example, keep in mind that null is neither false nor true or anything else. Null is nothing. That's the reason when we write a select query the correct syntax is

SELECT * FROM Orders WHERE IsPriorityOrder IS NULL

while the following query

SELECT * FROM Orders WHERE IsPriorityOrder = NULL

will not work properly.

 

Nullable types are different from their non-nullable equivalent. They are actually instances of the System.Nullable<T> struct. Using the '?' symbol after a type, is nothing more than a shortcut to the nullable type. Using nullable types when not needed, will make you take extra actions.

 

Nullable types, being different, may not directly be used with non-nullable types. So they contain the bool property HasValue. Checking that property, is similar to testing your nullable value for null. After that you can get its non-nullable type value by using the Value property. Here's what it looks like in case of bool types (it really makes no difference any type you choose).

 

bool nonNullableType = false;

bool? nullableType = null;

 

if (nullableType.HasValue)

//Check for if(nullableType != null) if you prefer

     nonNullableType = nullableType.Value;

     //You could also use nonNullableType = (bool)nullableType;

 

In case the bool? contained null value and the HasValue test was omitted, an InvalidOperationException would be thrown.

 

As you can see, using nullable types may cause bugs, which would have been avoided by non-nullable types, to show up. Take the following example. Even though bool types have no such problems, greater than and lower than operators will not work as expected when comparing nullable types. Comparing with null will return false; and that makes perfect sense since we can't tell what a value we don't know has to do with a value we do know. Look at the following example to see how things can get complicated in the third case where lower than returns false while not greater than returns true.

 

int? i = 4;

if(i < 5)

     ; // true

if(i > 5)

     ; // false

if(!(i > 5))

     ; // true

 

int? i = null;

if(i < 5)

     ; // false

if(i > 5)

     ; // false

if(!(i > 5))

     ; // true

 

Nullable types are used to insert null, as an extra option, when we don't know what the value is. Nullable Boolean types create a three-value logic type. Nullable values are better to be avoided, if not necessary, to avoid possible bugs as well.