Re: Lookup Tables, the right way ?

<jason_at_monitorsoft.com> wrote in message news:1142008567.819531.96590_at_u72g2000cwu.googlegroups.com...
> We are currently debating what the best way to implement lookup tables
> in our databases, and we there are two opposing optinions.
>
> 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 :

This debate often comes up with my colleagues too. I never cease to be amazed that the same people who will insist that we have "de-normalize for performance" will also insist on creating entirely spurious and unhelpful "lookup" tables that serve no purpose and only obfuscate the data.

Bob Hairgrove has this right except that he is insufficiently forceful about it. This is a no-brainer. If the column is colour and your glossary of colour names is fixed or only rarely expands, use a check constraint.

Roy Received on Fri Mar 10 2006 - 19:00:50 CET