Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: primary and foregin keys

Re: primary and foregin keys

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 25 Dec 2004 09:34:01 -0800
Message-ID: <1103996041.283463.284860@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 - 11:34:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US