Re: Abbreviation List Tables Design, aka OTLT
- From: "dawn" <dawnwolthuis@xxxxxxxxx>
- Date: 18 Jan 2006 14:18:24 -0800
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.
No, each lookup "file" is a separate item in a master file of files.
> 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.
> 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.
> 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.
> 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.
> 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.
> 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.
> 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)
> 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.
> 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.
> 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.
> 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
> 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.
.
- References:
- Abbreviation List Tables Design, aka OTLT
- From: dawn
- Abbreviation List Tables Design, aka OTLT
- Prev by Date: [PickSource] Current Survey -- 1/18/2006
- Next by Date: Re: Abbreviation List Tables Design, aka OTLT
- Previous by thread: Re: Abbreviation List Tables Design, aka OTLT
- Next by thread: Re: Abbreviation List Tables Design, aka OTLT
- Index(es):
Relevant Pages
|
Loading
