Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Also see dba.stackexchange.com/a/5066/630 please –  gbn Apr 17 '12 at 6:49
    
Good point mentioned in the above link about not being able to filter foreign keys, that's a consideration I didn't make. –  Adam Apr 17 '12 at 12:15
add comment

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
1  
I agree, the urge of combining type tables IMO is a typical case of premature optimization. First, the table that today have two fields tomorrow could have five, and second, in your table design you need to take into account if the data is related, not if the structure looks the same. If you combine Users and ProductTypes because they have "the same" structure of id/name, you probably end with a table that have both the fields "Telephone" and "BasePrice". –  Alberto Martinez Apr 17 '12 at 9:15
    
Both good points, I also truly gave thought to enum fields or even denormalizing a few of the the tables (since they are hard and fast set industry types and haven't changed in years even through the transition from paper to digital... the medical world :D) but I know that can be just as bad. Being primarily a developer, I know the one table approach requires less coding but it didn't "feel" right. I wanted to note that although I accepted this solution I also felt @BillThor's was very relevant and shouldn't be discounted because it's not flagged as the "accepted" answer –  Adam Apr 17 '12 at 12:21
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.