How to structure a table for additional data if a particular value of an enum/lookup table is chosen?

Question

I'd like to store additional data if a particular value is chosen in the enumerated or looked-up column in a table. The obvious option is to have an extra column in the table, but this will lead to a mostly empty column and seems to be like bad approach. Is there another option/method of doing this?

Answer 1

Multiple tables are one option. It's similar to object-oriented code, just with your database tables. Imagine you have a fruit object and the dropdown specifies what type of fruit: banana, apple, orange, watermelon.

So your fruit table would have your ID, Weight, Color, Price, IsOrganic, etc. fields. Then Banana might also have FruitID, Length, RipenessLevel, Brand, IsBundled, etc. Apple would have FruitID, Flavor, IsBagged, IsWaxed, etc. Orange would have FruitID, IsWaxed, Flavor, IsBagged, etc. Watermelon would have FruitID, CutType, IsSeedless, etc.

Obviously you could potentially add multiple levels here since apples and oranges have some common fields and that may or may not make sense, depending on your needs. This way, when you do a lookup of the fruit data, you look across both tables to get all data or just the Fruit table to get basic data.

Answer 2

The cleaner approach would be to have another table containing the mapping between rows in the original table and the optional values. For example, say you are storing employee records, so you have a table with 4 columns: Id, Name, Department, and SalesQuota. Now, the SalesQuota value is filled in only for rows where Department = "Sales".

Separate the table, so that you have your employee table with Id, Name, and Department, and a sales quota table with Id and SalesQuota. You can join the two tables on id, to get the original view, but at the same time, you don't have a mostly-empty column. You can join the two tables, or operate on each separately.

Search for "database normalization" online to get lots more articles on this topic.