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

From: ben brugman <ben_at_niethier.nl>
Date: Mon, 8 Mar 2004 17:02:09 +0100
Message-ID: <404c9900$0$273$4d4ebb8e_at_read.news.nl.uu.net>


"Ruud de Koter" <ruud_dekoter_at_hp.com> wrote in message news:404C7E5C.635C1EBC_at_hp.com...
> 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.

I do agree with you Ruud,
Having a single or limited number of tables and more codes, makes your code reusable without altering the database. You can reuse the code to fill the tables, and you can reuse the code to
read the tables.

If you have no or hardly constraints, this is probably a better economic way to work than introduce a table for each and every code.

Having a table for each code can have it's advantages as well, especially if you want to have more strict control and have requirements about the codes. (Thing like constraints or relationships.). Sometimes this advantage (allthough real) is overstressed, I think.

ben brugman

>
> >
> > 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
> --------------------------------------------------------------------------

Received on Mon Mar 08 2004 - 17:02:09 CET

Original text of this message