What are the design alternatives when having multiple "type" tables

Question

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?

Answer 1

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.

Answer 2

Type tables are a common feature of many systems. You may want to create a standard layout for your type tables. I prefer something like:

model_type {
    model_id int PK NOT NULL,
    description char UK NOT NULL,
    abbreviation char,
    sort_order int
    active indicator (boolean, end date, other appropriate type)
}

You could use the abbreviation or description as the PK, but then you need to do cascading updates when the value changes. If you want to add internationalization in the database the description, abbreviation, and sort order can be moved out into a child table with a language field. Otherwise, you can use your languages internationalization support.

The description field should be a natural key for the type table. You may also want to enforce uniqueness on the abbreviation, and in some cases they are an alternate natural key. I usually sort with the sort_order first (nulls last) to enable bubbling common or default values to the head of the list.

Tables containing types would have a foreign key reference to the appropriate types tables. Some types tables will be unique to a particular parent table, but other types are may be shared by two or more tables. If you see table with similar contents but different names take a second look. You may have two names for the same type, a subtype, or just similar types. Model and build accordingly.

Active indicator can be checked by a trigger, but is usually validated by code.

Some types may have associated data with can be added to their types table as additional fields.

Alternatives (none of which I recommend):

• Check contraints on the type field,
• Enum fields,
• A common types table with triggers to validate, or
• Ignoring types altogether.

With proper maintenance of the types tables, you can develop code for new types, and release it to production. The code won't be able to use the new type until the type is in the type table, and if selections (drop-down, etc) are built from the database, interactive code won't even try to use them.

You may want to consider whether to cache the type descriptive data and just retrieve ids, or do the joins to retrieve the descriptions.

Answer 3

Tables such as your first example are known as lookup tables. They are commonly found and useful. However, what you have named label would usually have a more meaningful name, such as user_type. A couple of asides: is it desirable to introduce a surrogate for such a simple table? If the surrogate key is used in other tables (foreign keys), then would a more meaningful name, such as user_type_id be more useful (e.g.allowing the use of NATURAL JOIN and similar)? Are you missing a natural key on the label attribute? Certainly the label attribute should not be nullable.

The second example (as @gbn identifies) is popularly known as one true lookup table (OTLT), is recognised as a design flaw and is widely contraindicated. Avoid.