Path: news.easynews.com!easynews!sjc-peer.news.verio.net!news.verio.net!sn-xit-01!sn-xit-02!supernews.com!postnews1.google.com!not-for-mail
From: 71062.1056@compuserve.com (--CELKO--)
Newsgroups: comp.databases.theory
Subject: Re: Multiple parent tables?
Date: 26 Oct 2001 17:40:49 -0700
Organization: http://groups.google.com/
Lines: 39
Message-ID: <c0d87ec0.0110261640.4dafb674@posting.google.com>
References: <3bc4ab7f.748408281@news-server.nyc.rr.com>
NNTP-Posting-Host: 64.154.151.177
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1004143249 20560 127.0.0.1 (27 Oct 2001 00:40:49 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 27 Oct 2001 00:40:49 GMT
Xref: easynews comp.databases.theory:18827
X-Received-Date: Fri, 26 Oct 2001 19:32:13 MST (news.easynews.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.