Re: Lookup Tables, the right way?

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

Designs evolve. What if you need to use color descriptions for other objects in the future? And if color descriptions change then? Use option 1.

EB Received on Thu Mar 16 2006 - 17:43:15 CET