Re: spliting shipment quantities

In article <VJVw4.84393$V_.50248_at_news1-hme0>, Peter Whalley <peter.carri lho_at_virginnet.co.uk> writes
>I am having problems designing a database.
>
>My table "Order Details" has the following fields:
>Order ID Product ID Qty ordered Qty shipped Etc.
>1023 12345 15 10
>
>I don't know how to split the order into goods that have been shipped and
>goods that have not been shipped, so that I am able to assess what goods are
>outstanding.
>
>Can anybody help me?

This table is only part of an order-processing system. You will also need a shipment table because you need to know what was shipped and when, so that you can raise paperwork for shipping and invoicing. You need to record all of the transactions that can take place.

Try looking at the life-history of an order and building a system that can handle all of the things that can happen. Order quantities can be changed, part-shipments take place, goods get returned for credit, goods get returned for replacement. What happens between the time when the computer generates shipping documents and the time the goods go out of the door?

Try tables for these, I've included *some* of the attributes you might want. There are other ways of doing this too.

Order:

        Item 
        Customer 
        Order-Date
        Customer's purchase-order number
        Order

Order-Line: 
        Order
        Line
        Item
        Qty Ordered
        Qty Outstanding
        Price
        Tax Code

Shipping-Line: 
        Order-Line
        Qty-Shipped
        Date/Time Record Created
        Date/Time Confirmed Despatched

Invoice: 
        Invoice-Number
        Date
        Total Value[1]

Invoice-Line: 
        Order-Line
        Item
        Qty-Shipped
        Price
        Date/Time Confirmed Despatch
        Tax Code

[1] It's OK to put a derived figure here as invoices should never be modified once they have been created.  

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk