Re: Categorized lookup tables?

I agree with Paul. Why so many people get their mind set on creating a nice "clean looking" schema is a mystery to me. The whole point of the exercise is to model the data properly, not to arrange data to meet a person's definition of "neat". Besides, lookup tables are your friends. They help explain otherwise arcane, unnatural data. Why make enemies out of tehm by making them work with arcane, unnatural data of their own?

"Joshua Caplan" <josh_at_technologist.com> wrote in message news:3b3ba487$0$62140$6dfeac65_at_news.en.com...
> I seem to run into this problem every time I'm doing data modelling.
>
> All the answers to every multiple-choice field in the system require a
> (compact) code and a descriptive text string. One table, called Lookup,
> with fields Code*, Type*, Description. In this scenario, even the lookup
> types themselves can appear in this table (provided "TYPE" has type
 "TYPE";
> sometimes getting that record in there takes some doing).
>
> Now I want to constrain my real data tables to use the Lookup table for
> multiple-choice fields. "Ethnicity", for example. But must I include
> Ethnicity Code and Ethnicity Type, so that I can set the pair up as a
> foreign key? The Ethnicity Type field would always contain the constant
> code for the ethnicity lookup type, and thus violates normalization
> principles. Without the Ethnicity Code field, I can't designate Ethnicity
> Code as a foreign key because there would be no unique index to serve as
> referential integrity master (there's no guarantee that the lookup codes
> will be unique across different types).
>
> I can hear you thinking, "just create an autonumber/sequence/identity
 field
> in Lookup and use that".
>
> Very funny. What if I have a distributed database, where central HQ
 invents
> and broadcasts Lookup codes, and the satellites send data back (keeping
> autonumbers in sync is a death spiral)? What if, in addition to the Code,
> Type, and Description, I have a Revision Number because these things are
> changing all the time and I need to keep old ones around for
> auditing/time-sensitive reporting purposes? What if I need really fast
> join-free yet comprehensible access to the data tables? The Code needs to
> be the link, here.
>
> Is there a database that allows me to put an expression (like a constant,
> for example), rather than a field name, as part of a foreign key? Is
 there
> one which allows me to constrain a field based on a subset of another
 table,
> or a view/query? Breaking these tables out into separate,
> schematically-identical tables not only clutters the hell out of the data
> model, it creates more mundane maintenance and development work with the
> creation scripts, constraints, permissions, indices, triggers, editors,
 ...,
> and inhibits the dynamic expansion of lookup types that "'TYPE' is a
 'TYPE'"
> facilitates.
>
> Josh
>
>
Received on Sun Jul 22 2001 - 01:32:32 CEST