Re: Multiple parent tables?

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 26 Oct 2001 17:40:49 -0700
Message-ID: <c0d87ec0.0110261640.4dafb674@posting.google.com>

>> I'm building an ordering/workflow system which involves, among
others,
these three entities: Orders, Items, Revisions. <<

Please post DDL instead of narratives. I would design this differently. I cannot understand an order without any items, as pre your specs -- perhaps they are reserved in advance? Next, a revision is not made to an order; it is made to an item.

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,  ...);

CREATE TABLE Order_Items
(order_nbr INTEGER NOT NULL

           REFERENCES Orders(order_nbr)
           ON UPDATE CASCADE
           ON DELETE CASCADE,

 item_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  item_nbr INTEGER NOT NULL,
 quantity INTEGER NOT NULL,
 ...
 PRIMARY KEY (order_nbr, item_date, item_nbr));

When you want to revise an item, put the new version into the table. The current data can be seen in the VIEW

CREATE VIEW Current_Items (order_nbr, item_date, item_nbr, quantity, ...)
 SELECT order_nbr, item_date, item_nbr, quantity, ...    FROM Order_Items AS I1
  WHERE item_date

• (SELECT MAX(item_date) FROM Order_Items AS I2 WHERE I1.order_nbr = I1.order_nbr AND I1.item_nbr = I1.item_nbr);

This keeps your history in one place and makes the queries a bit easier. Received on Fri Oct 26 2001 - 19:40:49 CDT