Categorized lookup tables?

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:26 CEST