Re: Lookup Tables, the right way?

Steve wrote:
> 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.

Maybe bigger (although the database could use compression under the covers). But it will be slower using the id, because in every query you need to join to the lookup table.
And creating compound indexes using the lookup value is a pain. Oh, and using the lookup value as part of a check constraint is a pain. Having to do the join in every single query is a pain.

> 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.

The fastest join is the one you don't need to do. Besides, I can't even measure the performance difference between using a numeric vs even moderate length strings in our databases (have used Oracle, SqlServer and Interbase).

> 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.

Almost by definition, a lookup name will never change...that's kind of the point. But if it really can, then of course you've got to design for it. But you are at the mercies of your specific product - use update cascade if available, or a mass update operation. Due to product limitation, you may even be compelled to use an Id field. Received on Sat Mar 11 2006 - 21:28:38 CET