Should I use null or a separate table?

I could have a nullable field or I could do basic normalisation, having another table which may or may not join to a row in this table.

My instinct was to use the second method, as this is the "right" (normalised) way of handling this. However, thinking about this, it can have drawbacks.

• The whole entity is not easily visible in its table (you have to - first find and then - jump to its linking tables)

• you now have to remember to join the table(s)

• It could lead to loads and loads of tables (I don't know if this is an issue for performance)

What do you usually do? It it worth creating a new table just to have an optional property for an entity?

Like all things, the answer is "it depends".

More tables <> bad if the tables are necessary for proper normalization. Splitting nullable columns off is something I do often, but not always. What factors into my decision are things like:

• How often will the column be NULL? If it's rarely populated, may make sense to have it as an optional column, implemented as its own table. This acts like a filtered index any time you might need to locate certain records.

• What is the context of the optional column(s)? If it's information you won't have until a later event occurs (shipping/payment info, etc), then logically it may make more sense for you to implement it separately from the parent table.

• How will the data be accessed/how wide is the parent table? I often deal with summary data from vendors that results in very wide tables with a lot of null values. I usually will split each column into its own table in order to speed up ad hoc queries that only need a small subset of the data.

StackExchange Database Administrators Q#273052, answer score: 6