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?

Improve this question
9
  • The question is interesting but abstract and vague. Can you give a concrete example of the problem you want to solve ...
    – Albert Godfrind
    Aug 2, 2020 at 7:46
  • @AlbertGodfrind This is a general question. A table with an optional field
    – Tobi Akinyemi
    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).
    – Albert Godfrind
    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.
    – Albert Godfrind
    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.
    – Albert Godfrind
    Aug 6, 2020 at 15:20

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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