Our plumbing supply store needs to keep track of its customers, including their names, address, city, state, zip code, and phone number (assume we do not have a zip code table). We also need to keep track of the products we stock, including name, description, and list price. At any given time, a customer may place an order which may consist of more than one product - for example, a single order could consist of a two lengths of pipe, a faucet kit, and a sump pump.

The solution we have seen to this problem uses four tables, CUSTOMER, ORDER, PRODUCT, and ORDERDETAIL (which according to several other standard conventions could also be called LINEITEM, ORDERPRODUCT, or QUANTITY), with prices stored in PRODUCT. Suppose that on April 25, we lower the price of the Deluxe Spraymaster system by changing the UnitPrice attribute of record P0024 in the PRODUCT table. If we then try to calculate the total price of an order placed April 18, it will reflect the new price, not the price in effect on April 18. That will be wrong unless we give the customer a retroactive refund.

Redesign the database so it stores the entire price history for each product -- that is, for each product, we store all prices we have ever charged, each with the date/time the price became effective.