Re: Lookup Tables, the right way?

"sonja" <none_at_none.com> wrote in message news:duvg0c$1q5$1_at_sycamore.fernuni-hagen.de...
> 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.
> >
> >
> > Maybe bigger (although the database could use compression under the
covers).
> > But it will be slower using the id, because in every query you need to
join
> > to the lookup table.
>
> 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.

My recommendation was in the context of the original poster who had a simple schema. I would not presume to imply that this advice is universal.

The only way to know for sure is to measure it, which it sounds like you have, and so have a basis for a rational design choice. However, my experience is that many developers choose sub-optimal approaches based on intuition, without actually verifying the necessity. I have performed tests which showed that avoidance of character key fields added significant complexity, yet did not improve performance, indeed, it often hurt performance due to additional joins being required. YMMV
> > 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?

Certainly, I was just emphasizing these aspects because they are often overlooked. Its an easy trap to optimize the parts, leading to a sub-optimal whole, especially for the softer requirements such as easy of development and maintenance. Received on Tue Mar 14 2006 - 08:07:56 CET