Re: Lookup Tables, the right way?
Date: 10 Mar 2006 09:46:15 -0800
Jason Williams wrote:
> 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 :
> 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:
For the example you gave, the second approach is the more theoretically sound.
A key is what defines the entity. Is it the case that cars can be either "red" or "blue", or is it the case that cars can be either color 1, which is currently "red" but we will later change to "yellow", thus changing the name of the color of a bunch of cars, or color 2, which is currently "blue" but which we plan on being "green" in 2007.
If the lookup table entries have further attributes which may change, thus updating all the entities that refer to that lookup table row, then approach 1 is the better choice. If the lookup is simply to constrain an entity to a list of predefined values, then 2 is the better choice.
Marshall Received on Fri Mar 10 2006 - 18:46:15 CET