Re: Any new thoughts on OTLT (One True Lookup Table)

Paul <paul_at_not.a.chance.ie> wrote in message news:<MPG.1ab05d632c3053219898e9_at_news1.eircom.net>...
> jinglebells5555_at_yahoo.com says...
>
> > I found a discussion in the archives that was a couple of years old.
> > I'm one man development team working on a small website. I'm designing
> > a database that will have scores of lookup tables and I'm thinking it
> > will be much quicker for me to have one "generic" one than 40
> > individual ones that are almost completely identical except for the
> > content.
>
>
> This makes no sense. If you have "things" that you want to lookup that
> are similar (title - Mr., Mrs., Rev., Col., Miss, Fr.) whatever, then it
> makes sense to keep those similar entities in the same table (obviously
> "sameness" will to an extent be defined by the application), but what is
> the problem with having lots of small tables rather than one large (and
> potentially messy) table?
>
> > Since I'm using postgres I've also thought about using inheritance but
> > that ties me to postgres.
>
>
> PostGres, schmostGres...AcCess, schmacCess it doesn't matter what RDBMS
> you are using. You keep similar things together, different things
> arpart.

I agree with you 100%. And I have seen this generic lookup table nonsense done often enough too, unfortunately. The justification seems to be that you only have to build one complex "maintain generic lookup" module rather than N very simple individual table maintenance modules. You then also have to "roll your own" datatype constraints on the lookup tables (when lookup_type is "TITLE" then lookup_code must be no more than 6 characters, etc.) and you can't use foreign keys for data integrity (unless you include the lookup_type column in the child tables). It is a false economy. Received on Thu Mar 04 2004 - 12:40:15 CET