Re: Lookup Tables, the right way ?

From: Bob Hairgrove <invalid_at_bigfoot.com>
Date: Fri, 10 Mar 2006 22:19:12 +0100
Message-ID: <p9r3121n3jlkq91v9unno42antuv3520vc_at_4ax.com>


On Fri, 10 Mar 2006 14:48:56 -0600, SD <sd_at_noemailspam.com> wrote:

>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?

Ever heard of "ON UPDATE CASCADE"?

--
Bob Hairgrove
NoSpamPlease_at_Home.com
Received on Fri Mar 10 2006 - 22:19:12 CET

Original text of this message