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

Ruud de Koter <ruud_dekoter_at_hp.com> wrote in message news:<40483031.1F7E8889_at_hp.com>...
> Hi Jingle,
> You wrote:
> >
> > 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.
> >
> > Since I'm using postgres I've also thought about using inheritance but
> > that ties me to postgres.
> >
> > Any thoughts?
>
> Contrary to the other reactions I regard your approach entirely feasible. Have
> been working with it since ages. There 's one thing you can do to make life
> easier if you go that road: make sure every single code has a unique
> (pseudo)key. Though there are people in this group who will try and flame me
> down for this suggestion, that does take care of the referential constraints
> issue, doesn't it?

I'll try REALLY hard not to flame you to a crisp for that, but... what are the great advantages of this approach? No, ensuring your (pseudo)keys are unique across the whole lookup table does NOT take care of the referential constraints issue - at least not in any meaningful way. Consider:

create table lookup
( id integer primary key
, domain varchar2(10)
, value varchar2(100)
);

create table person
( name varchar2(30) primary key
, gender_id references lookup
);

insert into lookup values (1, 'GENDER', 'MALE' );
insert into lookup values (2, 'GENDER', 'FEMALE' );
insert into lookup values (3, 'SALARY BAND', 'LOW' );
insert into lookup values (4, 'SALARY BAND', 'HIGH' );

The data is perfectly valid according to the constraints, but Smith's gender is apparently 'HIGH'.

Sorry, but this "stinks like a pair of armoured trousers after the Hundred Years War" (Blackadder). Received on Fri Mar 05 2004 - 13:04:04 CET