Re: Normalization and Derived Information
Date: 24 Sep 2004 12:39:49 -0700
Message-ID: <18c7b3c2.0409241139.1b76e22f_at_posting.google.com>
>> A trigger can compute the column and then it is always in sync. <<
But then the computed value takes up space and it is faster to compute it in main storage than to read it off the disk. Also, triggers are proprietary inspite of the existence of standards for them.
Let me some more things to the history table while I am here:
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
CONSTRAINT history_is_sorted
CHECK (start_date <= end_date)
CONSTRAINT periods_are_non_overlapping
CHECK (NOT EXISTS
(SELECT *
FROM PriceHistory AS P1
WHERE PriceHistory.upc = P1.upc
AND PriceHistory.startdate <> P1.startdate
AND PriceHistory.startdate BETWEEN P1.startdate AND
P1.enddate)),
CONSTRAINT one_current_price_per_product
CHECK (NOT EXISTS
(SELECT upc FROM PriceHistory AS P1 GROUP BY upc HAVING COUNT(startdate) <> COUNT(enddate) -1),..);
The table level constraints are legal in Standard SQL, but not implement in most products yet. Received on Fri Sep 24 2004 - 21:39:49 CEST