Re: Lookup Tables, the right way?

hi there,

I'm relatively new here and never wrote something here, (I read when time makes it possible for me) but you said something I do not agree.

dave schrieb:
> Steve wrote:
>

>>Marshall  Spight wrote:
>>
>>>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:
>>>>[...]
>>>

>>Your database would generally be bigger and slower if you store large
>>text strings in every individual record I think.

If it is such a small table, wich only consists of two columns, maybe you are right. But we experienced real bigger performance using ids instead of character fields at MS SQL Server having two concurrent database models. One based on variant 1 and the other variant 2. So I think its not a question of doing it for all tables, its a question of balance.
A problem may be that then the database model uses two concurrent models. But I do not think at this performance point it is an all or nothing question.

> And creating compound indexes using the lookup value is a pain.
> Oh, and using the lookup value as part of a check constraint is a pain.
> Having to do the join in every single query is a pain.

well, forgive me, I'm curious.
It depends on the systems requirements, doesn't it? Received on Sat Mar 11 2006 - 22:43:56 CET