Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Lookup Tables, the right way ?

Re: Lookup Tables, the right way ?

From: Bob Hairgrove <invalid_at_bigfoot.com>
Date: Fri, 10 Mar 2006 17:45:47 +0100
Message-ID: <uva3129d47r91f60jrki9tllvv6gnmuq9k@4ax.com>


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
Received on Fri Mar 10 2006 - 10:45:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US