The two models are not really alternatives of choice; each is governed by the
real world business model and only one is right for the business model which
applies.
1. Including the unit price in the OrderDetails table (which is how Northwind
does it BTW) is correct if the price per order can be varied ad hoc at any
one point in time, e.g. by preferential pricing for favoured customers
negotiated per order. In this case the unit price is functionally dependent
solely on the whole of the key of the OrderDetails table, so it is correctly
normalized. If, however, the unit price per product is fixed at any one
point in time regardless of the customer or any other factor, then it is not
functionally dependent on the key of OrderDetails, so the table is not
correctly normalized and there would then be nothing to prevent different
unit prices for the same product/date invalidly being entered in different
rows in the table.
2. Where the unit price per product is fixed at any one point in time
regardless of the customer or any other factor then it is functionally
dependent on the key of your Pricings table, i.e. the Product/DateFrom/DateTo
columns. So in this scenario your 'Plan B' is the correct one, joining the
pricings table to the Orders and OrderDetails tables on the date columns to
return the unit price which applies at any order date.
3. There is a third common scenario, which in effect combines the above two.
In this the unit price per product is fixed at any one point in time, so is
functionally dependent on the key of Pricings. Prices may be variably
discounted per customer, however, to produce the net unit price applied. If
the discount per customer is a fixed time independent ratio which applies to
all products then it is functionally dependent on the key of Customers, so is
correctly a column in that table (this is an unlikely scenario); if it varies
per product per customer but is time independent then it is an attribute of
CustomerDiscounts, so is modelled by a table with CustomerID, ProductID and
DiscountRatio columns (this is also unlikely); if it is a fixed ratio per
Customer which applies to all products but varies over time, then the key of
CustomerDiscounts is CustomerID, DateFrom and DateTo; if it varies per
product per customer and can change over time, then the key of
CustomerDiscounts is CustomerID, ProductID, DateFrom and DateTo (strictly
speaking only DateFrom is necessary in the last two situations, but it makes
things simpler to include both columns as the join is then on intersecting
ranges).
4. And there is also the question of discounting per quantity of course,
which may need to built into the model.
Ken Sheridan
Stafford, England
Jeff said:
Two approaches to consider, depending on your long-term information needs
and your experience (or comfort with learning new things).
Dave's approach is a common exception to the general rule that says not to
store that kind of data redundantly. But because the amount is dependent on
'point-in-time', it could be argued that you aren't storing data
redundantly.
The other common approach comes into play if you might have need now or
later to review price history. If so, and if you're feeling lucky (*or
brave*), your pricing table can include From and To date/time fields. Each
item gets stored in a separate "ITEM" table, then the pricing history in
your pricing table. You can see the same item stored many times in that
table, once for each change in price.
You can determine the unit price and 'extended price' of a quantity of the
item by knowing which item, and what order date (and looking up the
price-as-of-that-date in your price history table.
More complex, certainly. What is your current (or potential) business need?
Regards
Jeff Boyce
Microsoft Access MVP
I have a retail related DB and I realize that may not have thought through
some design elements. It involves orders. I have an Order Table, A Order
[quoted text clipped - 18 lines]
Any advise would be appreciated