Nullable Columns in a Database

Databases let us store information in an organized structure. Typically in the form of tables with rows and columns. Below is an example table with 3 rows and 5 columns that contains user information.

+---------+------------+-------------+-----------+---------------+
| User Id | First Name | Middle Name | Last Name | Date of Birth |
+---------+------------+-------------+-----------+---------------+
|       1 | Carice     | van         | Houten    | 05-09-1938    |
|       2 | Gulfaraz   |             | Rahman    | 23-03-1990    |
|       3 | Elon       | Reeve       | Musk      | 12-10-1979    |
+---------+------------+-------------+-----------+---------------+

Structure is achieved in the form of a table with rows and columns as the building blocks. Organization is achieved by adhering to a consistent pattern where each row holds one entry from each column and each column holds a value of a specific data type. The most common data types are — string, number and boolean. In the example table, column User Id is of type number; columns First Name, Middle Name and Last Name is of type string and column Date of Birth is of type date.

What are nullable columns?

Databases also let us store a lack of information in an organized structure. Columns accept empty or missing values instead of their assigned data type. The empty value in row 2 of the example table is possible because the Middle Name column accepts null values.

NULL represents a missing value or invalid value.

An empty room to illustrate the lack of something.

Need for NULL

When building a database, we try our best to obtain complete information but is not always possible. Regardless of the application, the following instances warrant the use of null values,

1. To represent missing information that needs to be collected in the future.
2. To represent optional information that does not need to be collected.
3. To represent invalid information that should be stored elsewhere or differently.

The meaning of null can also be designated based on the application. Such usage should be validated and documented as it deviates from the standard.

Consequences of NULL

If used for any reason, the use of null values has repercussions that need to be addressed to avoid undesirable consequences. 3-valued logic is a well-documented consequence which I will summarize as — it’s hard to follow. It’s hard to follow for both the developer and the user.

Consequences for the Developer

Consider the code snippet which reads from the example table,

let x = getMiddleNameByUserId(3) # x is assigned 'Reeve'
let y = getMiddleNameByUserId(2) # y is assigned null
console.log(x.toLowerCase()) # prints 'reeve'
console.log(y.toLowerCase()) # throws error
if (x) {
    console.log(x.toLowerCase()) # prints 'reeve'
}
if (y) {
    console.log(y.toLowerCase()) # does not throw error
}

An additional if-condition is needed to avoid the error when the function getMiddleNameByUserId can return a null. This additional check needs to be made anywhere Middle Name is used. Such (over)loads propagate to other layers of the application. When the use of nullable columns goes unchecked, then the code quickly becomes cluttered with easily avoidable conditionals.

Which increases the number of execution paths that need to be maintained while also making the code unreadable.

Consequences for the User

The manner by which null values are presented to the user changes the perceived utility of the application. When presenting the data from the example table,

1. If the interface chooses to hide properties with null values, the perception is that the application does not support Middle Names.
2. If the interface chooses to show properties with null values, the perception is that the application does not contain all the data it claims to offer.

Find a Balance

At the design phase, identify properties that need to support null as a value. Wherever possible, prefer to use a default value instead of a null. Where null is allowed, define clearly the meaning of null in that context.

The use of null is warranted when the information presented continues to make sense with and without the nullable properties.

In the example table, the Middle Name column appropriately supports null values as not everyone is given a middle name. It does not make sense if any other columns are stored in nullable columns as usually everyone is given a first name, last name and have a date of birth. Unjustifiable use of nullable columns like the Date of Birth column makes features like birthday notification more complex than they need to be.

Make an effort to understand what lack of information means in your application.

I recommend this SO answer as a starting point on the standard practice.

The use of NULL has an impact on SQL performance which can be leveraged.