Building the virtual OTLT
Date: Thu, 9 Sep 2004 09:48:34 -0400
Message-ID: <986dndg5yt7Hwt3cRVn-sg_at_comcast.com>
A few years ago, I was at a client site, dealing with the BANNER application. BANNER manages a wide variety of administrative data for a university. BANNER has a huge number of little tiny reference tables one for each different code type. I don't remember the exact number, but I'm going to use 185 in this post.
I wanted to be able to scan these codes as if they were in the "One True Lookup Table" (OTLT) that many newbie database designers arrive at as the best way to deal with code type proliferation without having to cope with table proliferation. Now I've argued VEHEMENTLY against the OTLT in this forum and elsewhere. So why on earth would I want a "virtual OTLT"?
Well, to answer that question, I'm going to repeat a dialogue that I posted
in here a while ago:
"We shouldn't have sent this guy a letter. He's an R73."
After dealing with this kind of repartee for a while, I decided that I
needed to be able to scan ALL of the Banner reference tables for "R73",
fairly quickly and easily. By "fairly quickly" I mean in about 2 minutes.
Abysmally slow by computer standards, but way ahead of the floundering
around that you get when you ask a human being.
"What the hell is an R73?"
"I have no idea. But we never send them any letters."
Well, I know my way around the Oracle data dictionary pretty well, and it
wasn't long before I had a list of 185 reference tables, where column one
was named like "foo_code" and some other column was named like "foo_desc".
I have this list driven template expander that I had written earlier for
such a time as this. It wasn't long before I had
185 select statements that looked something like this:
SELECT
foo_table
These 185 selects were spearated by 184 "UNION" operators. There you have
it. The mother of all unions, representing a virtual OTLT. I didn't
define a view, because I didn't have CREATE privileges on BANNER (a good
idea, IMO but that's another discussion). But it isn't hard to simulate a
view in scripts if you know what you are doing.
Now all I have to do is query this monster for a code value of "R73", and
it gives me the metadata I need to find R73 in BANNER, and a description
that may make sense to the humans. Hurray!
'FOO_TABLE' as table_name,
'FOO_CODE' as code_column,
foo_code as code value,
foo_desc as description
from
As soon as I got this built, I built another search tool called "Key Word
in Context" (KWIC). The KWIC search
scans the "DESCRIPTION" column for occurrences of a given word. Say the
word is "GOLF" (ignore case).
I quickly learned of all of the event types on campus that involved the
sport of golf, and how to find the instances.
I also learned of the fact that students and alumni had benefitted from a
OK, OK, it's not Google. But it was real good at the time.
The whole project took about 40 minutes between my last project of the
morning and my lunch break. I quickly became the go to guy for questions
like "What the hell is an R73?", or "find all the alumni who are now
Dentists".
So the virtual OTLT can be a useful tool, even though it illustrates bad
data management.
Received on Thu Sep 09 2004 - 15:48:34 CEST
"golf scholarship" (I kid you not). And that there was an endowment from a
major Golf equipment vendor.