0

If I have a table with multiple optional keys, is it better to have a single table with nullable keys or multiple tables with not nullable keys.

One benefit I could see is the separate tables would permit all keys to be not null which would be better for data integrity.

SINGLE_TABLE

KEY1       KEY2       KEY3      VALUE
-----------------------------------------
1          NULL       NULL      ValueA
1          2          NULL      ValueB
1          2          3         ValueC

or


TABLE_1

KEY1     VALUE
---------------
1        ValueA

TABLE_2

KEY1       KEY2     VALUE
--------------------------
1          2        ValueB

TABLE_3

KEY1       KEY2       KEY3      VALUE
-----------------------------------------
1          2          3         ValueC
6
  • is it better to have a single table with nullable keys or multiple tables with not nullable keys. Maybe yes, maybe no. I think one sparse table is better in most cases. Multi-table can save few disk space, of course, but your queries will be more complex. which would be better for data integrity. Field nullability is not related to data integrity in shown case. And constraint in sparse table can do the same if needed.
    – Akina
    Jul 9, 2019 at 11:51
  • How would you declare the key for the single_table example? Jul 9, 2019 at 18:51
  • @Lennart the keys are just numbers
    – TownCube
    Jul 12, 2019 at 18:06
  • No, what columns would be included in the key? Jul 12, 2019 at 18:29
  • Oh, so there is no key, they are just named that way. Key has a special meaning, you might concider naming them something else Jul 12, 2019 at 18:33

1 Answer 1

1

Based on your model, a single table with nullable keys is superior. To find all of the values in the value column, you have to join three different tables and maintain that join in any query operations -- including updates (if you add a key3 to ValueB, now you have to move the entire record to a new table: an insert plus a delete instead of an update).

Better to have null fields so your record -- and any additional columns associated with that record out beyond the Value column -- don't have to be migrated between tables by your business logic tier. Instead, direct update statements can manage these changes.

2
  • Thanks for the answer, if the characteristics of the data mean values would never gain/lose keys (ie. once in table_1 they would never move out), would your recommendation still hold true that a single table is best?
    – TownCube
    Jul 12, 2019 at 18:08
  • Yes, probably. It would help prevent unnecessary complexity.
    – CaM
    Jul 13, 2019 at 17:53

Your Answer

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

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