Re: Abbreviation List Tables Design, aka OTLT

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

Cheers,

Brian

Tony Gravagno wrote: 
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.


.

Relevant Pages

  • Re: Abbreviation List Tables Design, aka OTLT
    ... each lookup "file" is a separate item in a master file of files. ... > just un-normalized OTLT. ... Your ID can be the name of the file with the codes being associated ... It is almost always dismissed as a terrible design by ...
    (comp.databases.pick)
  • Re: Abbreviation List Tables Design, aka OTLT
    ... Differnt records in the fiel represent different lookup tables.... ... It is almost always dismissed as a terrible design by ... > Then code lists for everything with a "reasonable number of entries," ... > such as status codes, marital status, state codes, etc can all be in ...
    (comp.databases.pick)
  • Re: One Master LU Table vs. Many LU Tables
    ... One True Lookup Table ... I don't understand your proposed design. ... here's the example OTLT in the Celko example ... CurrentProject.Connection.Execute sSQL ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Relationships and lookup tables.
    ... those entities stored in lookup tables are not likely to have ... CustomerType, ... >> design of your application, the tables are the first things to ... >> orders, order details, cars, books, ideas. ...
    (microsoft.public.access.gettingstarted)
  • Re: nss_ldap and openldap on the same server.
    ... Look for Status codes and Actions ... Reading getgrouplistturns out the following: ... The getgrent() and getgrent_rfunctions make no attempt to suppress duplicate information if multiple sources are specified in nsswitch.conf. ... So after following the way through all man pages, it turns out that the behavior is fully correct as a lookup is done to find out all groups to which the specified slapd user belongs to. ...
    (freebsd-questions)
Loading