Database Answers Header Fishing Boat in Fog, Alaska
Order Tracking
Home Ask a Question Careers Data Models FAQs SQL Scripts Search Site Map  
Back to the Physical Data Model.

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

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'.

Barry Williams
Principal Consultant
Database Answers

[ Home Page | Ask Us a Question | Email Us | FAQs | Site Map ]

© IceBreaker WebDesigns 2002