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.