Creating a virtual OTLT (One True Lookup Table)

From: Laconic2 <laconic2_at_comcast.net>
Date: Sat, 27 Mar 2004 16:54:34 -0500
Message-ID: <E_-dnazIR8pqavjd4p2dnA_at_comcast.com>



WARNING: What follows is "skunk works". It works but it stinks. Don't expect me to defend this on general principles. I won't. I share it for the sake of those who may have a need.

It's possible to create what looks like a single lookup table by forming a union on the 40 or 100 or however many lookup tables in a database. I've been arguing, in the other thread, that multiple lookup tables are better. But there are times when you need to lookup a code, but you have no idea of what type of code it is, so you have no idea of which table to look in.

Well, taking the example of states and airlines from the other thread, consider this:

<code>

/* UNTESTED EXAMPLE */ create table STATES (

    STATE_CODE CHAR(2),
    DESC CHAR(40)
);

create table AIRLINES (

    AIRLINE_CODE CHAR(2),
    DESC CHAR(40)
);

create view CODES as
select

    'STATE_CODE' CODE_TYPE,
    STATE_CODE CODE,
    DESC DESCRIPTION
from STATES
union
 select

    'AIRLINE_CODE' CODE_TYPE
    AIRLINE_CODE CODE,
    DESC DESCRIPTION
from AIRLINES;

</code>

Now, you can use this view as the "one true lookup table" that will find the code "AA" for you, even if you don't know whether that's a code for a state or an airline. Note the column labelled "CODE_TYPE" . This is the third column that somebody called "CLASS" in the other thread. It's filled in by literal strings in the selection.

It should be obvious how this can be extended to a much larger number of tables. I devised this technique while on a contract. In about 20 minutes, I had concocted the mother of all unions, something that scanned about 175 tables to find any code you wanted. It stunk, but it worked. Received on Sat Mar 27 2004 - 22:54:34 CET

Original text of this message