Re: primary and foregin keys

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 25 Dec 2004 09:34:01 -0800
Message-ID: <1103996041.283463.284860_at_z14g2000cwz.googlegroups.com>


Try a design more like this. You have put split the products into two separate tables; in an RDBMS, one kind of entity should be in a one and nly one table.

CREATE TABLE ProductHistory
(product_nbr INTEGER NOT NULL,
start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, end_date DATE DEFAULT CURRENT_TIMESTAMP, -- null means current qty_on_hand INTEGER NOT NULL,
..
PRIMARY KEY (product_nbr, start_date));

Now use a view of the current status of your inventory:

CREATE VIEW Products ( product_nbr, ..)
AS
SELECT product_nbr, ..
  FROM ProductHistory
WHERE end_date IS NULL; Received on Sat Dec 25 2004 - 18:34:01 CET

Original text of this message