Re: Abbreviation List Tables Design, aka OTLT

I kind of agree with your comments..

Remembering that we don't use the ID structure that you describe.. Using
your example we would have 3 records: COLOR STATE and CUSTTYPE.. so

COLOR
<1>=R]B]G
<2>=Red]Blue]Green

etc.

I agree though that the format of the records should be simple - anything
more than a descriptive lookup will get confusing, though we do sometimes
store the odd attribute of extra info,

As far as abiding to laws.. I'm not quite sure how I would mathematically
describe when you stop using this type of stucture and go to a unique file
for the lookup. Personally, I still think along the lines of the old 32kb
item size limit!!!

As for performance, you would notice the difference if you used a lookup
structure as you describe and you had 10,000 different records in the file
(for different types of lookup) and you wanted a list of available
colours... I could do it in one disk read.. you'd have to scan 10,000
records to find it... We would both have the same data in the file...

Regards
Simon

"Brian Bond" <brian@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:11t2jo85k85ii16@xxxxxxxxxxxxxxxxxxxxx
> (Please prefix this missive with "IMHO". Thanks.)
>
> Many times I have encountered a file called "codes" that uses the type of
> "prefix*suffix" keys as is currently being discussed. All sorts of items
> typically get placed in such a file, e.g. COLOR*RED, COLOR*BLUE, STATE*WA,
> STATE*MN, CUSTTYPE*ABC, etc.
>
> Unfortunately, what often occurs with a this catch-all file is that one
> type
> of record needs some additional attributes, so attribute 2 for that type
> becomes used for some new specific piece of data related to the
> record type. Then the same thing happens for another type of record, where
> a
> different piece of data is now in attribute 2 for those records. And so
> on
> and so on. At some point you've ended up with many different types of
> data
> in a single file and things have become a mess.
>
> I will also ask, conceptually, where do you stop? How many records of a
> given type can be in this file before you decide that the data should be
> defined in its own file? Ten? Twenty? Heck, let's just put every single
> record in our system (keyed by prefix) into this file; then we only have
> one
> file to open! Going an insane step further, you could then just
> toss everything into the MD and not even worry about having ANY extra
> files <g>.
>
> We should also reference Codd's Rule 2 of "Guaranteed Access" (not that I
> agree with everything he says, but I do in this case) "All data should be
> accessible without ambiguity. This can be accomplished through a
> combination
> of the table name, primary key, and column name." So having attributes
> contain different data based in the type of key seems to me to violate
> this
> rule.
>
> So what do we do when we have "one off" pieces of data? What I have used
> in
> the past to good effect is to create a file called "system attributes",
> containing a single record which contains all the types of attributes that
> would typically
> be found in seperate records of a code file. This record can be read
> either at login, populating
> fields in named common, and/or specifically read at the top of an app.
>
> The record contains all one-off fields such as "COMPANY.NAME,
> COMPANY.ADDRESS, COMPANY.STATE, BUSINESS.LICENSE.NBR, CEO.IQ,
> PIZZA.DELIVERY.NBR, THIS.VALUE, THAT.VALUE, etc. The record may end up
> containing a boatload of attributes, but because it is read infreqently,
> this adds no significant processing overhead. Empty attributes are the
> equivilant of NOF.
>
> The nice aspect of using the system attribute record is that (1) every
> field
> is explicitly defined in a known location, (2) if need be (such as
> testing),
> it is easy enough to load in a different system attribute record and
> immediately change the behavior of your system.
>
> This record should not (generally) contain multi-value data, such as codes
> with corresponding descriptions. That type of data goes onto its own
> file,
> each value-pair corresponding to one record (adding that if you prefer
> having one record with a boatload of value-pair combinations to avoid
> reads, a reasonable alternative is to cache your data as you progress.
> When you need to check a given code file, first check a list of keys read;
> if the value exists, get whatever you need from a matching description
> list. If not, then read the appropriate record and add it to your list to
> avoid further reads. The extra overhead is minimal.)
>
> As you can probably guess, my #1 concern is the structure of the database.
> I prefer not to design to meet a performance goal, but rather that the
> database can accurately define my data. I have not in a very long while
> come up with a situation where the database design following the above
> concepts caused any performance problems, especially with today's fast
> hardware.
>
> Go Seahawks!
>
>
>
>


.

Relevant Pages

  • Re: LDAP Performance (long)
    ... Cache the slapd's internal database lookups in slapd memory. ... The first is the new TAG:key lookup, ...
    (comp.mail.sendmail)
  • Re: Same database or another?
    ... that I have used the lookup wizard provided. ... if I choose to add tables to database that have absolutely no relation to ... >> work toward the capture of the feral animals. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Database design question
    ... and what he is talking about is a single code ... Pro SQL Server 2000 Database Design ... > dont know the details of the lookup data. ... > What do we then do with the more "unknown" user-defined lookup data. ...
    (microsoft.public.sqlserver.programming)
  • Re: Lookup Tables and Field Validation Rule Properties
    ... I am in the process of designing my first database and plan to use this ... employees data and track down employees personal info, ... The tables in my original design contained many lookup fields but after ... The other question I asked was, instead of setting the above validation in the table level, can't I implement it in the Form Level. ...
    (microsoft.public.access.gettingstarted)
  • Re: tables in other file
    ... ++ Rename the new table ... ++ Select the table you just copied over into this database ... University of Maryland Baltimore County ... At this moment it is in the MP.mdb file, but I want it in the MP_data.mdb file, linked to the code file. ...
    (microsoft.public.access.setupconfig)
Loading