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

From: Laconic2 <laconic2_at_comcast.net>
Date: Fri, 26 Mar 2004 12:38:25 -0500
Message-ID: <ldWdnetusIfs9_nd4p2dnA_at_comcast.com>


I don't have anything to say about OTLT that's very new, but I do have a few thoughts that have been aired before.

Consider the following code and description pairs drawn from a travel reservation system,

<code>
("PA", "Pennsylvania")
("PA", "Pan American")

</code>

Obviously, if we are talking about states, we mean Pennsylvania, and if we are talking about airlines, we mean Pan American. But the question is, is it better to put these pairs in one table, or in two separate tables?

First off, there are two ways of looking at the above. here's one: There is one code, with two different meanings in two different contexts.

Here's the other one: There are two different codes, drawn from two different domains, that happen to be spelled exactly the same way (homonyms).

I take the second view.

Now, if you are designing a relational database, it's a really, really good idea never to put two values from different domains in the same column. AFAIK, it's a requirement. If you put state codes and airline codes in separate columns, you might as well go ahead and put them in separate tables.

If you are designing an object oriented database, I'll let someone else speak to that.

In follow on posts, I'm going to explain how you can automate (somewhat) the process of creating a new code table each time a new code type is defined, and how you can form a view that unifies them, in case you want to look up a code, but you have no idea of the context.

They are both very, very simple.

Cheers! Received on Fri Mar 26 2004 - 18:38:25 CET

Original text of this message