Re: DB Design Question

Thanks,

(and sorry to all - i don't know how the message got in this thread)

Makes sense. What do you mean by "line item" - the content of the order?

MSherrill_at_compuserve.com wrote in message news:<3b3f6fa5.19181631_at_news.compuserve.com>...
> 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)
Received on Sun Jul 22 2001 - 01:32:59 CEST