Re: Building the virtual OTLT

"Laconic2" <laconic2_at_comcast.net> wrote in message news: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."
> "What the hell is an R73?"
> "I have no idea. But we never send them any letters."
>
> 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.
>
> 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' as table_name,
> 'FOO_CODE' as code_column,
> foo_code as code value,
> foo_desc as description
> from
> 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!
>
> 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
> "golf scholarship" (I kid you not). And that there was an endowment from
a
> major Golf equipment vendor.
>
> 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.
>

I would say that you have the perfect setup. Individual LTs, which makes the data management correct, and a "view" of the whole shebang for certain practical purposes. Received on Thu Sep 09 2004 - 16:02:17 CEST