Re: Lookup Tables, the right way ?

On 3/10/2006 10:36 AM, 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.
>

Best to use 1. because what if you have subtle colors and you decide at some point to change the description? Received on Fri Mar 10 2006 - 21:48:56 CET