Re: Normalization and Derived Information

jcelko212_at_earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0409230608.28259799_at_posting.google.com>...
> >> 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 quibble (well, it's a rare opportunity): IIRC NULL is not guaranteed to sort high. some vendors sort low. some let you choose.

BobTheDataBaseBoy

  A VIEW on PriceHistory gives me the current
> pricing information for the application developers.
>
> --CELKO--
Received on Fri Sep 24 2004 - 03:18:25 CEST