5

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?

9
  • The question is interesting but abstract and vague. Can you give a concrete example of the problem you want to solve ... Aug 2, 2020 at 7:46
  • @AlbertGodfrind This is a general question. A table with an optional field Aug 2, 2020 at 11:57
  • An optional field is just a column that can be NULL. I see no reason to have a separate table for that - unless there are very specific and measurable issues with performance. Like one mention was a "long" field - one that contains, say, a user's resume or photograph. If 90% of the queries do not use those columns, then it might make sense to separate them out. However most of database systems store those out-of-line anyway, in dedicated data structures (CLOB or BLOB). Aug 4, 2020 at 15:03
  • So, again, unless there is an actual measurable problem in keeping information together, then I would advise against introducing more tables just based on theory and "best practices". It will make queries more complex by introducing unnecessary joins, and may bring performance questions. Aug 4, 2020 at 15:04
  • 1
    At some point, you have to sacrifice theoretical purity to practicality and ease of use. Denormalizing is a very common process when designing relational data models. Aug 6, 2020 at 15:20

1 Answer 1

6

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:

  1. 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.
  2. 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.
  3. 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.
1
  • Thanks for the answer, I think I'll split the (optional description) column into a separate table Aug 2, 2020 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.