Resolutions and/or Overrides

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Thu, 07 Oct 2004 10:19:55 -0400
Message-ID: <baj3kc.jog.ln_at_mercury.downsfam.net>



Folks,

In the past weeks I've gotten much value out of asking questions here and stating opinions that are subsequently challenged, so here goes another one.

I call the operation an "override" based on my view of how it works, but it was called a "resolution" in the shop where I saw the idea most fully developed.

The basic idea is this. The table of ITEMS (PK: ITEM) lists item WIDGET with a price of $100.00. Except that another table, TAB2, cross-references CUSTOMER_TYPE and ITEMS, and lists WIDGET as costing $95.00 for customer type "Dealer" and $90.00 for customer type "Distributor". It has no entry for the third customer type, "Retail." The rub is that the combination of customer type and item may or may not be in the cross-reference. If it is there, you use it, otherwise you don't.

Another simple example might be a purchasing system in which you have agreed upon prices for some items from some vendors, but otherwise you get on the phone and just go with the lower price and type it in manually. The price chosen is a function of two columns, the item and vendor, and there may or may not be an entry in the item-vendor table.

It gets more complicated, but the rest is just an embellishment of this basic idea.

The problem becomes, how do I know what price to put on an order? The shop where I saw this used so pervasively called this a "resolution" because they would do it procedurally, looking first in the second table and failing that falling back to the ITEMS table. Hence they were resolving the price.

Upon seeing this, and the performance troubles it gave, I tried to recast the problem and saw it as an "override", where I could have a view of the two tables wherein anything in table2 would override what was in table1. By pulling prices out of this view I could do it in only one pull, with the override logic buried in the view definition.

The only thing that really bugs me is that this is the only thing that shop did that surprised me, as by this point I thought I had seen everything. For some reason I never needed to do this myself, and I don't see a lot of people talk about it. Yet in their world, it always seemed to be the Right Thing, they had this all over the place.

Any stories on how you have handled these cases would be much appreciated.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me
Received on Thu Oct 07 2004 - 16:19:55 CEST

Original text of this message