Re: Normalization and Derived Information

>> If the price changes, that probably SHOULDN'T change the price
charged on sales that have been completed. <<

My solution for that is to keep a history in a table like this:

CREATE TABLE PriceHistory
(upc CHAR(9) NOT NULL,
 price DECIMAL (12,4) NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE, -- null means current
 ..);

CREATE VIEW PriceList (upc, price, ...)
AS SELECT upc, price, ...

     FROM PriceHistory
    WHERE end_date IS NULL;

then use "order_date BETWEEN start_date AND end_date" to get the price in effect at the time. A VIEW on PriceHistory gives me the current pricing information for the application developers.  

--CELKO-- Received on Thu Sep 23 2004 - 16:08:48 CEST