We are creating a lookup table that will handle multiple types of data. For simplicity, I'm only including 2, but there are actually 6. The table will store static data for each type.
We decided to put all the types in single table. The resulting data looks like the table below. But I'm wondering if it would be better to have multiple tables, one for each type.
When I suggested this, it was said that it would not make sense to have multiple tables with only a single row in each. Which I agree with, but having all these empty fields does not seem right either. Just wondering what is best practice?
type1 | type2 | type1_field1 | type2_field1 | type2_field2 |
---|---|---|---|---|
first type | type1_field1_data | |||
second type | type2_field1_data | type2_field2_data |
Some example queries:
SELECT type1_field1 FROM table WHERE type1 = 'first type'
SELECT type2_field1 FROM table WHERE type2 = 'second type'
SELECT type2_field2 FROM table WHERE type1 = 'second type'
SELECT l.type1_field1, t.type_data
FROM lookup_table l
INNER JOIN type1_table t ON l.type1 = t.type
SELECTs
.