Abbreviation List Tables Design, aka OTLT

#1

There have been a number of discussions in various database-related
forums about what is sometimes referred to as OTLT or One True Lookup
Table. It is almost always dismissed as a terrible design by
relational or SQL folks, likely for good reasons if you are thinking in
terms of constraints on attributes.

However, in the MV world, I have seen this design pattern several times
and was wondering how pervasive it is and whether it is considered good
design. My inclination is to think that it is a good way to design an
MV solution. Something like:

LIST DICT ABBREVLISTS ...
@ID.......... FORMAT S/M ASSOC
@ID 10L S
abbrev 10L M ABBREVS
shortDesc 20L M ABBREVS
longDesc 60L M ABBREVS
instructions 10L M ABBREVS

Then code lists for everything with a "reasonable number of entries,"
such as status codes, marital status, state codes, etc can all be in
this one file, each with a different record ID.

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?

Thanks. --dawn

P.S. I don't know yet if I'll write on this topic, but because
information from anything I read these days could get into my blog,
I'll state that overtly here and indicate that I would not quote anyone
from a list or newsgroup without first asking them.

#2

In my experience this is not a common pattern in MV, where each
instance of any type of lookup code is a separate item in a file.
However I have seen and designed "table of tables" files, where one
file has many list items, each with a unique structure. Maybe that's
just un-normalized OTLT. Almost all apps have some kind of Control
file or other adhoc file for keeping miscellaneous junk like next
sequential IDs and month-end dates, etc. Maybe that's considered
OTLT? But I don't think I've ever seen similar items broken down into
atomic records and then filed along with a bunch of other unrelated
data.

The closest I have seen is having a single Entity file which includes
Companies (customers, vendors, prospects) and People (employees,
owners, primary contacts, etc). PS/RD has used this structure for
years but other companies do as well. I like this Entity structure to
an extent because people move from place to place, and some people are
associated with different companies, some customers are also vendors,
some contractors are DBA companies but want their checks in their own
name, and it's easy to search for some name where you really don't
know if it's a person or company or how you are related to them. I
don't like it for the sheer mass of items it accumulates.

One other exception is having a single Transaction file that has lots
of different kinds of transactions that are processed by different
proggies. These might be EDI docs of different X12 document formats -
but all of them are transactions, maybe that's the same concept as
OTLT?

There is a bit of elegance to this OTLT notion, in that you only need
one file for most lookups. If you're doing a lookup on the Unit of
Measure for EA, then you have the one file to Open and maybe you read
for UOM*EA, rather than having a separate UOM file, and other
variables open to a wide assortment of other tables.

But in general the concept strikes me as being awkward for a couple
reasons: How do you select all states vs all status codes? Your ID
must be multipart or you need to have another field for "lookup type",
or you need another list of related lists. Also, as some lists grow
the developer might find it's more appropriate to break out some
lookups into their own files anyway. Well if you're going to have
some unique lookup tables plus one OTLT file, why not just keep
everything separate from the start? Some might consider it inelegant
to do some data types one way and others differently

Maybe in the end it's only about aesthetics anyway. Maybe the real
answer is that this is neither right nor wrong, just another tool in
the box to choose if it fits a specific project.

T

"dawn" wrote:

Quote:

There have been a number of discussions in various database-related
forums about what is sometimes referred to as OTLT or One True Lookup
Table. It is almost always dismissed as a terrible design by
relational or SQL folks, likely for good reasons if you are thinking in
terms of constraints on attributes.

However, in the MV world, I have seen this design pattern several times
and was wondering how pervasive it is and whether it is considered good
design. My inclination is to think that it is a good way to design an
MV solution. Something like:

LIST DICT ABBREVLISTS ...
@ID.......... FORMAT S/M ASSOC
@ID 10L S
abbrev 10L M ABBREVS
shortDesc 20L M ABBREVS
longDesc 60L M ABBREVS
instructions 10L M ABBREVS

Then code lists for everything with a "reasonable number of entries,"
such as status codes, marital status, state codes, etc can all be in
this one file, each with a different record ID.

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?

Thanks. --dawn

P.S. I don't know yet if I'll write on this topic, but because
information from anything I read these days could get into my blog,
I'll state that overtly here and indicate that I would not quote anyone
from a list or newsgroup without first asking them.

#3

Let's just hope that Luke doesn't have to index such a table! <g>

Cheers,

Brian

Tony Gravagno wrote:

Quote:

In my experience this is not a common pattern in MV, where each
instance of any type of lookup code is a separate item in a file.
However I have seen and designed "table of tables" files, where one
file has many list items, each with a unique structure. Maybe that's
just un-normalized OTLT. Almost all apps have some kind of Control
file or other adhoc file for keeping miscellaneous junk like next
sequential IDs and month-end dates, etc. Maybe that's considered
OTLT? But I don't think I've ever seen similar items broken down into
atomic records and then filed along with a bunch of other unrelated
data.

The closest I have seen is having a single Entity file which includes
Companies (customers, vendors, prospects) and People (employees,
owners, primary contacts, etc). PS/RD has used this structure for
years but other companies do as well. I like this Entity structure to
an extent because people move from place to place, and some people are
associated with different companies, some customers are also vendors,
some contractors are DBA companies but want their checks in their own
name, and it's easy to search for some name where you really don't
know if it's a person or company or how you are related to them. I
don't like it for the sheer mass of items it accumulates.

One other exception is having a single Transaction file that has lots
of different kinds of transactions that are processed by different
proggies. These might be EDI docs of different X12 document formats -
but all of them are transactions, maybe that's the same concept as
OTLT?

There is a bit of elegance to this OTLT notion, in that you only need
one file for most lookups. If you're doing a lookup on the Unit of
Measure for EA, then you have the one file to Open and maybe you read
for UOM*EA, rather than having a separate UOM file, and other
variables open to a wide assortment of other tables.

But in general the concept strikes me as being awkward for a couple
reasons: How do you select all states vs all status codes? Your ID
must be multipart or you need to have another field for "lookup type",
or you need another list of related lists. Also, as some lists grow
the developer might find it's more appropriate to break out some
lookups into their own files anyway. Well if you're going to have
some unique lookup tables plus one OTLT file, why not just keep
everything separate from the start? Some might consider it inelegant
to do some data types one way and others differently

Maybe in the end it's only about aesthetics anyway. Maybe the real
answer is that this is neither right nor wrong, just another tool in
the box to choose if it fits a specific project.

T

"dawn" wrote:

There have been a number of discussions in various database-related
forums about what is sometimes referred to as OTLT or One True Lookup
Table. It is almost always dismissed as a terrible design by
relational or SQL folks, likely for good reasons if you are thinking in
terms of constraints on attributes.

However, in the MV world, I have seen this design pattern several times
and was wondering how pervasive it is and whether it is considered good
design. My inclination is to think that it is a good way to design an
MV solution. Something like:

LIST DICT ABBREVLISTS ...
@ID.......... FORMAT S/M ASSOC
@ID 10L S
abbrev 10L M ABBREVS
shortDesc 20L M ABBREVS
longDesc 60L M ABBREVS
instructions 10L M ABBREVS

Then code lists for everything with a "reasonable number of entries,"
such as status codes, marital status, state codes, etc can all be in
this one file, each with a different record ID.

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?

Thanks. --dawn

P.S. I don't know yet if I'll write on this topic, but because
information from anything I read these days could get into my blog,
I'll state that overtly here and indicate that I would not quote anyone
from a list or newsgroup without first asking them.

#4

Tony Gravagno wrote:

Quote:

In my experience this is not a common pattern in MV, where each
instance of any type of lookup code is a separate item in a file.

No, each lookup "file" is a separate item in a master file of files.

Quote:

However I have seen and designed "table of tables" files, where one
file has many list items, each with a unique structure. Maybe that's
just un-normalized OTLT.

Yes.

Quote:

Almost all apps have some kind of Control
file or other adhoc file for keeping miscellaneous junk like next
sequential IDs and month-end dates, etc. Maybe that's considered
OTLT?

No, that is more of a parm file or control file, often just one or more
records of misc data.

Quote:

But I don't think I've ever seen similar items broken down into
atomic records and then filed along with a bunch of other unrelated
data.

The closest I have seen is having a single Entity file which includes
Companies (customers, vendors, prospects) and People (employees,
owners, primary contacts, etc).

That is a common approach even within the broader community, where you
have a Party (or Parties) file that could have both people and
organizations inheriting its properties.

Quote:

PS/RD has used this structure for
years but other companies do as well. I like this Entity structure to
an extent because people move from place to place, and some people are
associated with different companies, some customers are also vendors,
some contractors are DBA companies but want their checks in their own
name, and it's easy to search for some name where you really don't
know if it's a person or company or how you are related to them. I
don't like it for the sheer mass of items it accumulates.

One other exception is having a single Transaction file that has lots
of different kinds of transactions that are processed by different
proggies.

Yes and I have been looking for my notes that I now suspect are some
that I lost with my stolen computer. I found a term for what I have
called the "M-card pattern" used in by-gone days where a record comes
in designated as a master card (M), with transaction cards (T), perhaps
multiple per master card, and then perhaps other types of transactions
such as an address change designated with other "card types". I saw
something in an LDAP description that reminded me of that and found a
more modern name than "M-card" but forget what that was.

Quote:

These might be EDI docs of different X12 document formats -
but all of them are transactions, maybe that's the same concept as
OTLT?

No -- the file of files is what OTLT resembles, except that the
relational folks have to break it out into one file-code per row where
in MV it can be one file=one record/item.

Quote:

There is a bit of elegance to this OTLT notion, in that you only need
one file for most lookups. If you're doing a lookup on the Unit of
Measure for EA, then you have the one file to Open and maybe you read
for UOM*EA, rather than having a separate UOM file, and other
variables open to a wide assortment of other tables.

Yes. One thing you gain is that you can have a single maintenance
screen for all such lookup files.

Quote:

But in general the concept strikes me as being awkward for a couple
reasons: How do you select all states vs all status codes? Your ID
must be multipart or you need to have another field for "lookup type",

Your ID can be the name of the file with the codes being associated
multivalues (that is what I was trying to show in the DICT listing)

Quote:

or you need another list of related lists. Also, as some lists grow
the developer might find it's more appropriate to break out some
lookups into their own files anyway.

Yes, I would guess that if you have some very large (and someone who
knows the internals better than I could quantify that perhaps) code
file, then it is likely better to split it out into its own table for
performance reasons.

Quote:

Well if you're going to have
some unique lookup tables plus one OTLT file, why not just keep
everything separate from the start?

I guess you could have a single update screen that asks which file it
is for and keep them all separate. However, it seems like there is
some economy of scale here in using one file for many. For example,
you could have new lookup files added through the UI without creating a
new file. I don't know why that would matter, however.

Quote:

Some might consider it inelegant
to do some data types one way and others differently

Maybe in the end it's only about aesthetics anyway.

It might be one of those eye of the beholder things, but there are
defnitely differences for the developer over time in doing it one way
over the other, so it seems to me that one approach is likely to be a
better bang for the buck than the other. I can't settle on which is
better in my own mind, however, but I lean toward a single file of
files for codes, perhaps just to tidy up the place. That isn't
supposed to be how database theory works, however, so I'd like to come
up with a better reason than that.

Quote:

Maybe the real
answer is that this is neither right nor wrong, just another tool in
the box to choose if it fits a specific project.

I agree that neither is right or wrong, but I do suspect one is better
than the other over the course of time. Hmmm. Thanks. --dawn

Quote:

T

"dawn" wrote:

There have been a number of discussions in various database-related
forums about what is sometimes referred to as OTLT or One True Lookup
Table. It is almost always dismissed as a terrible design by
relational or SQL folks, likely for good reasons if you are thinking in
terms of constraints on attributes.

However, in the MV world, I have seen this design pattern several times
and was wondering how pervasive it is and whether it is considered good
design. My inclination is to think that it is a good way to design an
MV solution. Something like:

LIST DICT ABBREVLISTS ...
@ID.......... FORMAT S/M ASSOC
@ID 10L S
abbrev 10L M ABBREVS
shortDesc 20L M ABBREVS
longDesc 60L M ABBREVS
instructions 10L M ABBREVS

Then code lists for everything with a "reasonable number of entries,"
such as status codes, marital status, state codes, etc can all be in
this one file, each with a different record ID.

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?

Thanks. --dawn

P.S. I don't know yet if I'll write on this topic, but because
information from anything I read these days could get into my blog,
I'll state that overtly here and indicate that I would not quote anyone
from a list or newsgroup without first asking them.

#5

Hi Dawn
We use this all the time for those umpteen little things that need to be
specified but do not need their own table. Each of our major package
components has a Descriptions file, the key is a two part attribute with a
couple of characters indicating the grouping and the second part being the
item specific key.
This way the client can easily add new types of a particular thing and all
the programs will instantly recognise it. We of course do not let the
client define the prefixes as we need that control and we do not let them
access system control items.
It has another benefit, one can rapidly list all those odd control headings
simply by sorting by prefix.

We also have another Control file for those elements that the system must
keep track of that has a variety of items with different types using
appropriate keys. The client is not allowed near this.

I have worked on a lot of disparate systems over the last 30 years and I
cannot remember one that did not use this method.

Regards
Peter McMurray

"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote

Quote:

There have been a number of discussions in various database-related
forums about what is sometimes referred to as OTLT or One True Lookup
Table. It is almost always dismissed as a terrible design by
relational or SQL folks, likely for good reasons if you are thinking in
terms of constraints on attributes.

However, in the MV world, I have seen this design pattern several times
and was wondering how pervasive it is and whether it is considered good
design. My inclination is to think that it is a good way to design an
MV solution. Something like:

LIST DICT ABBREVLISTS ...
@ID.......... FORMAT S/M ASSOC
@ID 10L S
abbrev 10L M ABBREVS
shortDesc 20L M ABBREVS
longDesc 60L M ABBREVS
instructions 10L M ABBREVS

Then code lists for everything with a "reasonable number of entries,"
such as status codes, marital status, state codes, etc can all be in
this one file, each with a different record ID.

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?

Thanks. --dawn

P.S. I don't know yet if I'll write on this topic, but because
information from anything I read these days could get into my blog,
I'll state that overtly here and indicate that I would not quote anyone
from a list or newsgroup without first asking them.

#6

I don't know about "common place", but I have seen worse! with a single
item having multiple-lookup "tables" for different things. Apparently
the idea was that by reading a single item you had access to all of the
commonly used lookups.

(FWIW we enhanced Visage so that it could see these "structures", and
also files with different record structures based on key components, as
"virtual files". I have seen this last structure much more, with @IDs
prefixed with say "title*", "terms*", "relationship*" etc, and a
seperate record for each value)

The big downside with the approach is that you can't "easily" access
your abbreviation using native query facilities, unless you go & write
some "smart" basic code, which kinda defeats the purpose I would have
thought.

Certainly if I stumbled across this code my immediate urge would be to
"fix it"

YMMV, but I would NOT suggest using this as a "nice feature" of MV

#7

Excalibur wrote:

Quote:

Hi Dawn
We use this all the time for those umpteen little things that need to be
specified but do not need their own table.

There's the rub. What do you mean by "do not need their own table".
By what criteria do you decide that something needs its own table?

Quote:

Each of our major package
components has a Descriptions file, the key is a two part attribute with a
couple of characters indicating the grouping and the second part being the
item specific key.

I'm not tracking why a two-part key unless you are classifying your
code files in some way such as FINANCIAL*VENDOR-TYPES. Or are you
normalizing your file of files so that you have the key as
VENDOR-TYPES*GOV with the description field being Government or some
such?

Quote:

This way the client can easily add new types of a particular thing and all
the programs will instantly recognise it.

How would this be different from users adding new records to a file for
each type of thing, given that the programs are only going to recognize
the "things" that are defined in either case? In other words, even if
you have a program that permits users to add a new "code file" for
example, VENDOR-STATUS, no program is going to recognize those codes
until they are set up to look at the VENDOR-STATUS record for the
codes, right? I'm missing something.

Quote:

We of course do not let the
client define the prefixes as we need that control and we do not let them
access system control items.

Instead of adding a prefix, you could add another file, right? If your
program asked for a file name instead of a record name, wouldn't that
have the same effect? I'm not actually challenging you because I think
you are wrong, but because I think you are right in doing this, but I
don't know why.

Quote:

It has another benefit, one can rapidly list all those odd control headings
simply by sorting by prefix.

Yes. Good one. That has to do with the housekeeping of knowing what
type of files you have -- most or all of the code files in one place
means you and users can easily make a listing of them all, without
changing the report when new ones are added.

Quote:

We also have another Control file for those elements that the system must
keep track of that has a variety of items with different types using
appropriate keys. The client is not allowed near this.

Yes, often called a control file or perhaps parameter file. These can
be killers for normalization.

Quote:

I have worked on a lot of disparate systems over the last 30 years and I
cannot remember one that did not use this method.

The file of files method? Yes, that is what I thought. While
considered poor practice in the relational world, I suspect it is
standard fare (from what I have seen) in MV systems. Thanks. --dawn

Quote:

Regards
Peter McMurray

"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote in message
news:1137606300.956463.129220 (AT) g14g2000cwa (DOT) googlegroups.com...
There have been a number of discussions in various database-related
forums about what is sometimes referred to as OTLT or One True Lookup
Table. It is almost always dismissed as a terrible design by
relational or SQL folks, likely for good reasons if you are thinking in
terms of constraints on attributes.

However, in the MV world, I have seen this design pattern several times
and was wondering how pervasive it is and whether it is considered good
design. My inclination is to think that it is a good way to design an
MV solution. Something like:

LIST DICT ABBREVLISTS ...
@ID.......... FORMAT S/M ASSOC
@ID 10L S
abbrev 10L M ABBREVS
shortDesc 20L M ABBREVS
longDesc 60L M ABBREVS
instructions 10L M ABBREVS

Then code lists for everything with a "reasonable number of entries,"
such as status codes, marital status, state codes, etc can all be in
this one file, each with a different record ID.

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?

Thanks. --dawn

P.S. I don't know yet if I'll write on this topic, but because
information from anything I read these days could get into my blog,
I'll state that overtly here and indicate that I would not quote anyone
from a list or newsgroup without first asking them.

#8

Ross Ferris wrote:

Quote:

I don't know about "common place", but I have seen worse! with a single
item having multiple-lookup "tables" for different things. Apparently
the idea was that by reading a single item you had access to all of the
commonly used lookups.

Now THAT sounds downright ugly, but is, of course, similar. Wish I
could figure out what all of my criteria are for choosing the approach
of lookup files as records/items compared to files compared to
fields/attributes.

Quote:

(FWIW we enhanced Visage so that it could see these "structures", and
also files with different record structures based on key components,

that's the "M Card Pattern" for which I need to find that better name

Quote:

as
"virtual files". I have seen this last structure much more, with @IDs
prefixed with say "title*", "terms*", "relationship*" etc, and a
seperate record for each value)

Yes, I've seen that too. Not a big fan of it, I must say.

Quote:

The big downside with the approach is that you can't "easily" access
your abbreviation using native query facilities, unless you go & write
some "smart" basic code, which kinda defeats the purpose I would have
thought.

Some clever virtual fields would likely go far, I would think.

Quote:

Certainly if I stumbled across this code my immediate urge would be to
"fix it"

You mean the file of files or the item of files?

Quote:

YMMV, but I would NOT suggest using this as a "nice feature" of MV

I'm looking at data models and it appears that wherever there are
non-relational data models, this pattern shows up. XML document and OO
objects can end up with similar approaches. If I am understanding your
take on this, you think it is better to have many files with little in
them, such as:

MaritalStatus
M Married
S Single
W Widowed
D Divorced

ThisStatus
A Active
P Pending
D Denied

ThatStatus
C Current
L Lapsed

Than OTLT with three records, such as

AbbrevLists
MaritalStatus M Married, S Single, W Widowed, D Divorced
ThisStatus A Active ...
ThatStatus C Current ...

Correct? --dawn

#9

I use a "lookup" file in a slightly different way...

Differnt records in the fiel represent different lookup tables.... using
multi-values for 3-dimensionality...

eg

<0> STATES
<1> NY]NJ]CA
<2> NEW YORK]NEW JERSEY]CALIFORNIA

etc

This has the benefit that the whole lookup table can be accessed in one disk
read, rather than having to index or select records. Unfortunately, you
can't do the lookup from coded data to description in a standard
dictionary - you need to use a program due to lack of "locate" in a
translate!

HTH
Simon
"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote

Quote:

There have been a number of discussions in various database-related
forums about what is sometimes referred to as OTLT or One True Lookup
Table. It is almost always dismissed as a terrible design by
relational or SQL folks, likely for good reasons if you are thinking in
terms of constraints on attributes.

However, in the MV world, I have seen this design pattern several times
and was wondering how pervasive it is and whether it is considered good
design. My inclination is to think that it is a good way to design an
MV solution. Something like:

LIST DICT ABBREVLISTS ...
@ID.......... FORMAT S/M ASSOC
@ID 10L S
abbrev 10L M ABBREVS
shortDesc 20L M ABBREVS
longDesc 60L M ABBREVS
instructions 10L M ABBREVS

Then code lists for everything with a "reasonable number of entries,"
such as status codes, marital status, state codes, etc can all be in
this one file, each with a different record ID.

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?

Thanks. --dawn

P.S. I don't know yet if I'll write on this topic, but because
information from anything I read these days could get into my blog,
I'll state that overtly here and indicate that I would not quote anyone
from a list or newsgroup without first asking them.

#10

There are only two kinds of lists: those that are all the same and those
that aren't. The vast majority of lists have only ID/Description. Beyond
that, it's really a matter of how you want to access the elements: locate or
read.

Our company uses a single Master Control File (MCF) to contain all readable
lists (not select/sselect) and other "control items". As you say, it keeps
everything in one place.

Mark Brown
Drexel Management Services, Inc

"dawn" <dawnwolthuis (AT) gmail (DOT) com> wrote

Quote:

Do you/your company use this approach? Would you consider this a
Pick/MultiValue best practice even if it is not a good practice in the
relational world? Why or why not? What are the pros and cons to using
this approach?