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

Everyone who says that creating 40 lookup tables, and maintaining them, is just too much work needs to consider automating that work, provided they agree that the work is worth doing.

What you need is an object in your system, that I'll call the "code manager".

Here's how the code manager works:

If you give it a "code type" and a "code value" and ask for a description, it uses the code type to figure out which code table to look in, and only looks in that table.

If you give it a "code value" but no "code type", it checks in its list of code tables, and does a lookup in all of the code tables. It returns a list of descriptions, with each description attached to a code_type, so the requestor can make use of that information.

If you give it an existing "code type" and a new "code value" and "description", it uses the "code type" to figure out which lookup table to put the new entry in, and inserts the code value and description into the correct lookup table.

If you give it a new "code type" (and maybe a description of the new code type) It spits out an SQL "CREATE TABLE" command that will make a new lookup table, and updates its own table of lookup tables.

The "CREATE TABLE" command can be passed for human review, or executed automatically, depending on a choice of system design.

I'm going to skip over updates and deletes of lookup information.

Is this too hard to program?

In the world of an object oriented system, you get an object interface that behaves like any other object. In the world of the database you get data that is easier to understand and use. That's the value.

Balance the value against the cost, and decide whether to do it. Received on Sat Mar 27 2004 - 14:38:21 CET