Re: Normalization and Derived Information

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 24 Sep 2004 12:39:49 -0700
Message-ID: <18c7b3c2.0409241139.1b76e22f@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 - 14:39:49 CDT