Re: DB Design Question

On 30 Jun 2001 11:41:57 -0700, nickgieschen_at_hotmail.com (nick) wrote:

>I need a DB which contains orders made by customers. Also, if the
>customer changes an order, I don't want to modify the original order,
>because I want to keep the old versions of the orders so that I can
>have a history of the changes.

Ok.

>So, I assume, I should treat each
>modification as a new order.

It's simpler to treat changes as changes.

A change is either a change to an order, a change to an order detail, or both. (Probably both.)

>My problem is how to represent this in
>the database.

Off the top of my head, you probably need tables like this:

 Orders
   OrderChanges (for changes to orders)    OrderLineItems
     LineItemChanges (for changes to line items)

 Orders
 --
 OrderID
 <...>
 Primary key (OrderID)

 OrderLineItems
 --
 OrderID (foreign key, references Orders.OrderID)  LineItemNum
 <...>
 Primary key (OrderID, LineItemNum)

 OrderChanges
 --
 OrderID (foreign key, references Orders.OrderID)  ChangeNum
 ChangeDateTime
 <...>
 Primary key (OrderID, ChangeNum)

 LineItemChanges
 --
 OrderID (foreign key, references OrderLineItems.OrderID)  LineItemNum (foreign key, references OrderLineItems.LineItemNum)  ChangeNum
 ChangeDateTime
 Primary key (OrderID, LineItemNum, ChangeNum)

-- 
Mike Sherrill
Information Management Systems