What are the design alternatives when having multiple “type” tables

best practices

I am working on a database design and see myself with lots of *_type tables (e.g. user_type, product_type, etc.) where the structure of these tables is the same, basically:

user_type (
    id int pk
    label char 
)

I could simplify this by doing something like:

labels (
    id int pk
    label char
    context blah
)

but is this an appropriate way to do this?

Best Answer

Please don't combine the various _type tables. Your future self (and anyone that ends up writing queries against the tables) will thank you.

  • If you combine the tables, you give up the ability to have referential integrity constraints ensure that your tables have valid data. Inevitably, someone will inadvertently insert a row that doesn't reference a valid row in your lookup table. Then, when you to run a report, you'll find that you have customers in all 61 states with folks in interesting states like 'NA' and 'X'.
  • If you combine the tables, you make it much harder for future developers to write queries.

I would much rather write something like

SELECT p.product_name,
       pt.product_type_name,
       ct.color_name
  FROM product p
       JOIN product_type pt ON (p.product_id = pt.product_id)
       JOIN color_type   ct ON (p.color_id   = ct.color_id)

than

SELECT p.product_name,
       pt.label product_type_name,
       ct.label color_name
  FROM product p
       JOIN (SELECT *
               FROM labels
              WHERE context = 'PRODUCT_TYPE') pt ON (p.product_id = pt.id)
       JOIN (SELECT *
               FROM labels
              WHERE context = 'COLOR_TYPE') ct ON (p.color_id = ct.id)
  • If you have separate tables, the optimizer is much more likely to be able to make good decisions. You'll inevitably have some lookup tables that have half a dozen rows and others that have hundreds of rows. If you have separate tables, it's relatively easy for the optimizer to figure out which lookups are more restrictive and which are less restrictive. If you throw all the _type tables together, the database will tend to have much less information to work with so it will be much less likely to pick the most efficient plans.

Beyond that, I completely second @BillThor's suggestions for how to create the various lookup tables.

Related Question