Re: Multiple parent tables?

Frank Enos wrote:
>
> I'm building an ordering/workflow system which involves, among others,
> these three entities: Orders, Items, Revisions.
>
> An Order can contain 0..n Items.
> An Order can contain 0..n Revisions
>
> Simple enough: a Parent/Child relationship between Orders/Items, the
> foreign key of Items being the primary key of Orders.
>
> Same for Orders/Revisions.
>
> Here's where it seems complicated:
> A revision is actually made up of 3..4 Items. That is, a Revision has
> inherent data, specifically its date, and contains 3..4 instances of
> data, each identical to an Item, except that the foreign key of the
> items would, in this case, refer to the primary key of the Revision
> instead of to an order.
>
> It seems redundant to put Items belonging to Orders in a separate
> table from Items belonging to Revisions (which in turn belong to
> Orders), but since I'm stuck as far as how to implement a foreign key
> in Items which can refer *either* to Orders or to Revisions, I'm
> suspiscious that I might be modeling the problem poorly in the first
> place.

One idea that comes to my mind is making the first order revision 0: Table Order containing only real head data (e.g. the customer) that doesn't change for different revisions.
Table Revision 1..n under Order.
Table Item 1..n under Revision.
This way you have a uniform way of access to an item.

If there is (nearly) no data that can't change in revisions, you can drop the table Order alltogether and simply include a reference to the previous revision in the table Revision. (You might want to rename Revision to Order then).

hth,
Heinz Received on Thu Oct 11 2001 - 08:18:00 CEST