Re: Categorized lookup tables?

What is it about the "one true lookup table"(OTLT) approach that is so appealing? I've seen this approach crop up time and time again and I just don't get it.

One of the problems I've seen with OTLTs is the code that must be written in order to maintain them. For example, it is always necessary to write extra filtering on the "type" attribute at a minimum. You've also pointed out another problem in your post. On the other hand, the brief negative coverage you've given to the obvious alternative, which is to create a lookup table when one is needed, doesn't seem to hold up to inspection.

"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).
 <snip>
> 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.

Let's take these one at a time

1. Cluttering the hell out of the data model.

Consolidation of lookup tables creates a data model that doesn't fit the data being modeled. This is an artifical simplification that hides the true nature of the data. As always, the true nature of data tends to come back and bite you later on. The typical problem you see with the OTLT approach is that new attributes are identified for some entities in the lookup table. This is usually handled in the OTLT by adding generic columns that are used differently depending on the "TYPE" column. The only way to validate and handle these columns correctly is - you guessed it - write more code.

2. Mundane maintenance and development

I've seen plenty of mundane code that was written to mainatin an OTLT.

3. Creating scripts

You've got me here. Creating fewer tables means less time spent on creation scripts. Given your total project budget, how much do you allocate to writing creation scripts? How much of that do you save by moving to an OTLT? How does that compare with amount of time you spend writing "TYPE =" logic?

4. Constraints and permissions

OTLTs are a benefit here if and only if the constraints and permission of all lookups are identical. In particular, I would expect constraints to vary depending on the lookup type in most cases. This is a perfect example of what you gain by not using an OTLT.

5. Triggers

Using triggers with lookup tables seems a bit exceptional, and I would expect that any such trigger in an OTLT would probably have to include conditional code for the "TYPE" attribute. Lookup table triggers are probably more common with OTLT schemas because of the problems noted in (4).

6. Editors

I concede that an editor for all lookup tables in a system would be easier to implement using an OTLT. What I'd like to know is: since when are we buliding editors for lookup tables? My impression is that lookup tables, by their nature, are not updated frequently.

7. Dynamic Expansion

I don't think I understand what you mean by this. It sounds like your saying that since lookup table types are also contained in the lookup table, that it is possible to add new lookups without adding a table. Of course, a new lookup type is unlikely to do you any good unless you write at least some type specific code to make use of it, right?

I've never used this approach in my own designs, and the presence of many lookup tables has never bother me. On the other hand, I have had the unpleseant experience of maintaining OTLT systems. I urge you to consider taking a straightforward approach where lookup tables are concerned.

Paul Received on Sun Jul 22 2001 - 01:32:29 CEST