Back to the Physical Data Model.

Things of Interest :-
1. Customers
2. Invoices
3. Orders
4. Products
5. Shipments

DISCUSSION OF ORDER TRACKING AND BACK ORDERS
The Problem :-
We have an e-commerce system that we developed in house that we need
to modify to accomodate back-orders.

Currently when�an order is completed it is�marked as processed.
But if we have a couple of back ordered items come into inventory we need to send them to
the person who ordered them but include a new tracking number to and invoice for
the remaining products.

So need to store the new tracking number and invoice info too.

The suggestion is :-
"To add one column to our Order table called ParentOrderID and any back ordered items
gets transferred to this new order and the ParentOrderID column filled in with the
original order's unique identifier (OrderID).

This way it is possible to treat�each part order that is fullfilled and shipped as a
unique order but still have it exist under one parent order."

A more appropriate solution is a general version of the approach outlined above,
based on Shipments.

1) It would seem confusing to have Orders and ParentOrders.
   Therefore, introduce the concept of 'Shipments'.
2) Normally, each all Items in one Order would go as one Shipment.
   But an Order could be associated with zero, one or many Shipments.
3) Each Item can appear on zero,one or many Shipments.
4) Each Shipment has its own Invoice number.
5) The Status of each Order is recorded, for example, 'Processed', 'Part Processed'.
6) The Status of each Item in an Order is recorded, for example, 'Item Shipped', 'Item Not Shipped'.