Re: Categorized lookup tables?

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

select * from View_Lookup where TypeDesc = 'MYTYPE' select * from MyTypeLookup

Not a whole lot of difference--except I have one table, instead of many (sometimes hundreds).

> You've also pointed out
> another problem in your post.

It's actually the same problem, and a valid one. So-called "modern" DBMSs don't support table partitioning, which has broad application beyond my simple example of the lookup table. If they did support it, I would not need code to maintain the relationships between it and the data.

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

This depends on the data being looked up, obviously. In my case, I find that certain "phrases" or "concepts" require records unto themselves and references from other records. Very rarely do I need extra "attributes"; if I do, I'm not dealing with a lookup anymore. I'm dealing with real data, so it stands to reason that it gets its own table. Generic columns are a bad idea, and I eschew them in favor of one-to-one relationships to tables with specialty information.

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

But if it's done well, it's done once--and it's never as redundant as the other approach. Data modelling and computer databases exist to reduce the menial or repetitive labor of people.

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

"TYPE =" logic is no more time-consuming to write than typing out the specific lookup-table name, as mentioned above. In the case of a distributed database with new branches opening all the time, I need creation scripts to be quick to fix and easily deployed.

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

Or what you lose, in my case(s). The only constraint for me is that the Type of a lookup also be a lookup. No variation; all lookups need a type.

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

The addition of a new row to a table with a system-assigned key requires a trigger in Oracle, unless you are willing to hard-code NEXTVAL into your business objects, instantly rendering them vendor-specific.

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

This impression is false. Our clients are always adding and revising the values in these tables and they require an application to facilitate this revision.

> I don't think I understand what you mean by [dynamic expansion]. 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?

In your approach, a new multiple-choice field is added to a data table, a new table is added to hold the allowable values, the values are entered into the new table (by hand, using SQL or something), and a constraint is established between the two. My way, a new multiple-choice field is added to a data table, a new record with type 'TYPE' is added to Lookup, the values are entered into Lookup (using the existing editor), and a constraint is established between the table and Lookup. Which way is easier?

The problem I bemoaned in my original post crops up here, but I contend that the failure here is in the product(s) itself, not the data model. Identically structured data does indeed merit uniform data modelling, but for some reason the major database vendors still have not been able to implement the full power that set-theoretic relations afford. Your criticisms levelled against OTLT are duly noted, but believe it or not, OTLT would be the pragmatic way in a lot of situations, except for the fact that it does not seem to be supported by DBMS vendors (because foreign key constraints are so restrictive).

I would like to know if there are any answers to the original questions I asked, namely:

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

Josh Received on Sun Jul 22 2001 - 01:33:33 CEST