I would appreciate any suggestions for a design that supports Change Orders.

Client has Orders>Order_Lines. PDF of Order is signed and accepted by client and is then locked from edit. However, an item needs to be cancelled, replaced, qty edited. I need to maintain integrity of original Order. Orders, btw, can have 1 or more Invoices (items are invoiced as they ship).

I have come across this post: http://stackoverflow.com/questions/11963386/table-design-to-keep-track-of-change-orders-in-a-customer-order-system?rq=1

which is the closest post I can find on the topic.

tia,

Barbara

This seems like it can be handled with some sort of version control. For CANCELS or VOIDS, you would simply change a "Status" field  of the original Order. 

For revisions, you would need a parent ID to tie the related versions together and another field (revision_number). You can can, also, use the "Status" field to define the "accepted" revision.

That is my off the cuff opinion without really seeing the data structure. I am not sure if there are other requirements that would disqualify my suggestion. 

Anyway, I hope it helps. Good luck!

Thank you for chiming in. This seems to be a bit of a rabbit hole, and I want to see if I can accommodate the requirement as simply as possible.

Void and Cancel for the entire Order is simply a status or flag in the parent Order record, agreed.

It can get complicated fast. For example, original order_item quantity is 6. PO is issued for 6. Client decides to change order to 3. I am thinking that each order_item would need a relationship to its original order_item version. Or, we simply invoice only 3 and ship only 3, then ultimately return 3 to vendor.

I'll post on TechNet, too. I'm sure I'm reinventing the wheel here.