Re: Lookup Tables, the right way ?

On 10 Mar 2006 08:36:07 -0800, "jason_at_monitorsoft.com" <jason_at_monitorsoft.com> wrote:

>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 :
>
> Table Cars
>
> Description ColorID(fk)
>
> Ford Escort 1
> Mini Metro 2
>
>
> Table Color
>
> ColorID(fk) Color
>
> 1 Red
> 2 Green
>
>
>
>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:
>
>
> Description Color(fk)
>
> Ford Escort Red
> Mini Metro Green
>
> Table Color
>
> Color(fk)
>
> Red
> Green
>
>
>I would appreciate your opinions.

The best approach would involve a domain, but maybe your particular RDBMS doesn't support domains?

What about a table with a single column containing the values? With either of the above approaches, you still need a unique index on the non-key column because otherwise, duplicates could still be entered into the lookup table.

For a very small number of values, a CHECK constraint with an "IN (...)" clause could also work. This is how I would try to do it first.

--
Bob Hairgrove
NoSpamPlease_at_Home.com