Single table or multiple tables for optional keys 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
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.