Re: primary and foregin keys

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