Re: Lookup Tables, the right way?

"Steve" <stevedaniel4_at_yahoo.co.uk> wrote in message news:1142096038.271481.90730_at_e56g2000cwe.googlegroups.com...
> Marshall Spight wrote:
> > Jason Williams wrote:
> > >
> > > 1. If you are defining a database table that requires a lookup table,
> > > then the foreign key between the tow tables should be an integer, and
> > > the lookup table should contain two columns, a sequence number that is
> > > used for the foreign keys, and the actual value. For example :
> > > [...]
> > > 2. If you are defining a database table that requires a lookup table,
> > > then the foreign key between the two tables should be a character
> > > field, and the lookup table should consist of a character field of the
> > > same length. For example:
> > > [...]
> >
>
> Your database would generally be bigger and slower if you store large
> text strings in every individual record I think.
>
> You could also use small or single character strings as your linking
> fields with a longer description stored in your lookup.
>
> Join operations, selections, updates etc will work faster on smaller
> strings or numeric values.
>
> Also if you need to change one of the text values for a field if you
> store the texts in each individual record, you have to go through and
> update them all. If you store them only once, you update them only
> once.
>
> S
>

PMFJI. The topic starter scrolled off the server before I could respond.

There are really several issues rolled into one here.

First, there's the question of surrogate keys versus natural keys. The word "red" sounds harmless enough. But wait till the marketing folks get a hold of the color names. We used colored paper in my office. what one vendor calls "Red", another vendor calls "Re-entry Red" (what a concept!). Whenever you use natural keys, you run the risk that the people who use them will run amok with them. If you can live with that, fine.

Second, there's the question of integer keys versus text keys. Yes, you will get a slight performance advantage with integer keys with most engines, but it isn't enough of a difference to drive this design decision.

Third, there's the issue of whether lookup tables are used only for code validation, or whether they are used for code translation as well. Are the reports, extracts, and other outputs going to refer to the colors as "1", "2", and "3"? I think not.

Fourth, there's the issue of drop down list design at the data entry screens. Do you plan on driving those lists off the lookup table? If so, how do you keep the drop down list in synch with the table?

All of the above could enter into the debate between the two options above. Received on Tue Mar 14 2006 - 13:12:49 CET