Chapter 7
Pages 185-87
Figure 7-2

Remodeled approach to discounts

CREATE TABLE prices
  ( product_code  VARCHAR2(10) NOT NULL
  , date_from     DATE         NOT NULL
    CONSTRAINT prices_df CHECK (date_from = TRUNC(date_from)
  , date_to       DATE         NOT NULL
    CONSTRAINT prices_dt CHECK (date_to   = TRUNC(date_to)
  , price         NUMBER       NOT NULL
  , CONSTRAINT prices_date_range CHECK (date_from <= date_to)
  , CONSTRAINT prices_pk PRIMARY KEY (product_code, date_to)
  , CONSTRAINT prices_fk FOREIGN KEY (product_code)
                               REFERENCES  (products)
  );

CREATE TABLE discount_sets
  ( product_code  VARCHAR2(10) NOT NULL
  , date_to       DATE         NOT NULL
  , CONSTRAINT disc_set_pk
        PRIMARY KEY (product_code, date_to)
  , CONSTRAINT disc_fk FOREIGN KEY (product_code)
                            REFERENCES (products)
  );

CREATE TABLE discounts
  ( product_code  VARCHAR2(10) NOT NULL
  , date_to       DATE         NOT NULL
  , qty_from      NUMBER(8,0)  NOT NULL
  , qty_to        NUMBER(8,0)  NOT NULL
  , discount_pct  NUMBER(2,2)  NOT NULL
  , CONSTRAINT disc_pk
        PRIMARY KEY (product_code, date_to, qty_to)
  , CONSTRAINT disc_fk FOREIGN KEY (product_code, date_to)
                                 REFERENCES (discount_sets)
  , CONSTRAINT disc_qty_range CHECK (qty_from <= qty_to)
  );

/* sample query to get discount which applies to
   :qty of :pcode sold on :sdate */

SELECT discount_pct
FROM   discount_sets s
     , discounts     d
 WHERE s.product_code = :pcode
 AND   s.date_to     >= :sdate
 AND   d.product_code = s.product_code
 AND   d.date_to      = s.date_to
 AND   d.qty_to      >= :qty;