Re: Abbreviation List Tables Design, aka OTLT

Like everything, it's all down to choosing the right tool for the job..

I'll use a single muti-value lookup as described only when the number of
possible choices is relatively small (<100) and particularly, where quick
data-validation and fast display of lookup tables (drop down list boxes in a
gui for example) are of benefit. The whole lookup table can be read in in
a single disk read and the drop-down is displayed very very quickly. The
list should only be used for validation of data or to provide
"more-information" to other data (such as Product Grouping, Order Category
etc)

On the other hand, for product lists etc. this obviously doesn't fit the
definition. Each product in the list is very unique and is most certainly
not just a cross-reference from a short code. In this scenario, building a
multi-value lookup would be bad practice.

We do use composite items in this way for invoices (multiple product codes,
qtys, sale prices) and for customer records (invoice history etc). I don't
think though that this really fits into the above two categories, and is a
key benefit of the MV architecture.

For invoices, we actually break a second relational rule - we hold
sufficient data in the invoice file to fully print a copy of the invoice
without referencing any other data... This means a copy of the customer name
and address, all the descriptions of the product codes (even though they
could be looked up) etc. This is done for 2 reasons ..

Firstly, performance, producing a copy invoice is a single disk read again.
Secondly, breaking this "relation" means that we don't suffer from the
potential problem of copies not being identical to the original (such as the
customer moving house, the copy of the original invoice should show the same
address that was on the original). Codd would turn in his grave!

We actually find a second interesting benefit of repeating data in multiple
files... Data corruption in one file almost always means it can be rebuilt
from another. There is always a "master" file for each type of record which
the othes "slave" off - we never have the scenario whereby when data changes
we need to update multiple copies of the data - this would be wrong! It is
a sensitive balance sometimes!

Regards
Simon

regards
Simon

"Excalibur" <excalibur21@xxxxxxxxxxx> wrote in message
news:NHZzf.221532$V7.40488@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi Brian
> It all depends how you define type.
> In my descriptions each record /item is of the same type.
> The key contains a meaningfull prefix and suffix.
> The data consists of a Short description, a Long Description and a Flag -
> is
> it a system controlled item or a user controlled item. I honestly cannot
> see that the system would be improved by breaking this into another 150+
> files.
>
> I would be interested to hear how you would define an invoice file.
> Personally I define an invoice as a document contaning all the relevant
> data
> in one Item/Record. I have seen Header and Line files with the lines
> stored
> as one item or split as an item per line.
>
> The very worst example I have ever seen is a Prices file containing one
> item
> Prices. The item contained the code, name, price etc of every item in the
> inventory as a single multi valued attribute. The thousands of stock
> items
> led to an interesting item that I believe could qualify as one of Dawn's
> lists. Definitely bad practice.
>
> Regards
> Peter McMurray
> "Brian Bond" <brian@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:11svju1prhgk6cb@xxxxxxxxxxxxxxxxxxxxx
>> I would like to refer you to the film "Amadeus". When the King complains
>> that one of Mozart's compositions has "too many notes", Mozart replies
> that
>> it contains exactly the number of notes required, no more and no less.
>>
>> I believe that the same principle holds true for database design. Use as
>> many tables as it requires to define your data. Yes, you may at times
>> end
>> up with a database design that has a lot of files with perhaps only a
> small
>> number of records in each. But each field has a defined location with a
>> specific meaning. As such, you end up with exactly the number of files
>> and
>> fields you require to for your database, "no more, no less".
>>
>> The alternative is to have a vaguely defined "code" file that too often
> ends
>> up containing bytes that can hardly even be categorized as data, since
> many
>> different types of data occupy the same attribute.
>>
>> You might argue that dictionaries can be constructed that take different
>> prefixes into account to determine what-is-what. But just keeping
> everything
>> seperate is a lot easier to do.
>>
>>
>
>


.

Relevant Pages

  • RE: Excel Formula or access ?
    ... "Kevin Vaughn" wrote: ... set up the lookup table? ... How would I have to sort the data to use the lookup? ... but if you could set up a lookup table with Invoice numbers and their ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Excel Formula or access ?
    ... set up the lookup table? ... How would I have to sort the data to use the lookup? ... but if you could set up a lookup table with Invoice numbers and their ... For sales tax reporting we need to know the state, city, county district ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Excel Formula or access ?
    ... "Kevin Vaughn" wrote: ... set up the lookup table? ... How would I have to sort the data to use the lookup? ... but if you could set up a lookup table with Invoice numbers and their ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Relate to a nonprimary key column
    ... I don't think that you are after a lookup. ... you might propagate it to Invoice value, ... > Thanx for your advice Miha, ... >>> possible related invoice was created. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Abbreviation List Tables Design, aka OTLT
    ... Regards ... for product lists etc. this obviously doesn't fit the ... > codes, qtys, sale prices) and for customer records (invoice history etc). ...
    (comp.databases.pick)