developer.* Blogs - Ideal Design/Structure for Lookup Tables - Comments

Localization Strings

Daniel Read — Wed, 21 Mar 2007 19:11:34 -0700

Support for multiple languages is an excellent suggestion, and one I will have to work into the main post at some point.

My first reaction is that is depends on what kind of localization scheme you are using (assuming that is the context you have in mind). For example, I have seen many schemes where there is a localization token associated with a "resource," which might be something that needs to appear in a label, or a descriptive field, or a dropdown list or listbox (all which could apply to lookup tables). So instead of, or in addition to, the Name and Description columns in the lookup table, you could have NameToken and DescriptionToken, and the middle or presentation tier in your system would translate that token to the appropriate language at runtime. Something like this:

CustomerType
    CustomerTypeID
    CustomerTypeName
    DefaultName
    NameToken
    DefaultDescription
    DescriptionToken
    Active
    DisplayOrder
    ...etc...

If instead you need your language support to be at the database level, you might create a Language lookup table with a row for each language you need; then you would have child table to the lookup table that had the lookup table's primary key as a foreign key. Then you could have an entry in the child table for translated value corresponding to the parent lookup table row, with of course a foreign key from the Language lookup table. Something like this:

Language
    LanguageID
    LanguageName
    CharacterSet

CustomerType
    CustomerTypeID
    CustomerTypeName
    DefaultName
    DefaultDescription
    Active
    DisplayOrder
    ...etc...

CustomerTypeLanguage
    CustomerTypeLanguageID
    CustomerTypeID
    LanguageID
    TranslatedName
    TranslatedDescription

I introduced the "Default" idea because I encountered a high traffic web site once that served 98% of its pages in English, yet every single page view did a full localization lookup on every resource.

Anyone else have thoughts on this one?

Dan

Multiple Language Requirements

Willy — Wed, 21 Mar 2007 15:38:45 -0700

Useful article. Any insight into best approach for handling multiple language requirements?

Display Logic

rgreathouse — Wed, 21 Mar 2007 14:14:13 -0700

What happens when your client wants to support multiple languages? I generally do not like having display logic fields (name, sort order) in the database. These are view attributes and should probably be evaluated in the view layer.

Table of Tables

Rich LaMarche — Wed, 21 Mar 2007 13:26:08 -0700

I also found the term Table of Tables commonly used for what you describe as a MUCK table.

-Rich

GUID PKs

Daniel Read — Tue, 20 Mar 2007 16:25:42 -0700

Coincidentally, the very next day after I wrote about using GUIDs as primary keys, Jeff Atwood added a (per usual) good article on the topic to the Coding Horror blog.

Dan

MUCK Tables

Daniel Read — Tue, 20 Mar 2007 11:27:30 -0700

Thank you, Ron, for that summary! I have encountered MUCK tables before in applications I have been tasked to maintain or rewrite (I've also heard them called "code tables"). In fact, I've seen some *huge* MUCK tables. The worst part of the evil is that (in the cases I've seen anyway) you have no referential integrity at all.

I'm glad to know that I would probably agree with Don Peterson's article, though the term "lookup table" has always seemed a fine one to me.

Dan

Lookup Table Madness

Ron Porter — Tue, 20 Mar 2007 07:32:10 -0700

Thanks for the good article, Dan. About the only difference between your structure and the one I've evolved is that you have a field called Active, whereas I have one called Obsolete. It feels good to have somebody agree with my decision!

As it happens, I already had an account at the site with article on Lookup Table Madness, so I've taken the liberty of summarizing it here. The only direct quote is indicated as such and the summary and examples are my own.

The author, Don Peterson, refers to something called "Massively Unified Code-Key tables, or MUCK tables." A MUCK table is basically a structure that allows for the inclusion of multiple 'lookup types' in one table. Thus, instead of separate tables for DocumentStatus and DocumentType, you end up with a table called LookupType with 2 entries (DocStatus and DocType) and a second table with the actual lookup values, keyed by LookupType.

I'm sure that we've all been down that road before! At least I hope I'm not alone in that particular folly. While a MUCK table certainly makes it easy to add new lookups, the reality is that the application needs to be recoded anyway if the new data is to be utilized. In practice, I've seen first hand how quickly this concept leads to attempts to use the RDBMS to create a program that very poorly simulates an RDBMS.

Peterson also dislikes the term 'lookup table' because there is nothing particularly special about the table in the first place. It contains data, it's related to other tables, it's used for reference and validation, etc., in the same way that things like Customer and InventoryItem are used.

Ideal Design/Structure for Lookup Tables

Daniel Read — Mon, 19 Mar 2007 14:36:30 -0700

In my work over the years that I have continually strived for the ideal structure of a lookup table. I must have designed hundreds of them by now in at least four different RDBMS engines. I may have finally arrived at a structure I really like, so I thought I would share it, with the hope that other people may benefit from adopting it, or even better, suggest improvements to the design.