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

You 're right, Tony. It keeps me from throwing away codes that are still being used, but that 's about everything. Didn't think it thru.

The great advantage..... Hm, I can have another try: in a packaged application this schema can be used to allow for a certain flexibility without breaking open the whole database schema. Support departments typically want to have as little difference as possible between the versions of a product running at different customers. Creating one table in which a customer can define his own codes and groupings of code might help in achieving this. It comes at a limited price, though.

Ruud.

>
> 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' );
>
> insert into person( name, gender_id ) values ('SMITH', 4);
>
> 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).

-- 
--------------------------------------------------------------------------------------
Ruud de Koter                    HP OpenView Software Business Unit
Senior Software Engineer         IT Service Management Operation
Telephone: +31 (20) 514 15 89    Van Diemenstraat 200  
Telefax  : +31 (20) 514 15 90    PO Box 831
Telnet   : 547 - 1589            1000 AV  Amsterdam, the Netherlands
Email    : ruud_dekoter_at_hp.com

internet: http://www.openview.hp.com/products/servicedesk
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------