Re: Database Design Question

Eoin Fitzpatrick wrote:
> I am trying to design a Sales Order Processing section to a database and
> I am stuck. A customer can place multiple orders for varying quantities of
> the same item. e.g.
> Order # 1001 Quantity = 1.
> Order #1002 Quantity = 2.
> Order #1003 Quantity = 5
> = total of 8 on order.
>
> The goods are invoiced but for availability or whatever reasons only 6 of
> this particular item are invoiced, which leaves 2 still on order
>
> I need to be able to track the 1 of the 6 invoiced refers to order #1001 , 2
> of them refer to order #1002 and the remaining 3 belong to order #1003. I
> also need to show that there are still 2 pieces outstanding from order #1003
> and, that all the 6 invoiced have been allocated to various orders.

You didn't deserve the diatribe from Leandro...at least, I don't think you did.

What are your rules relating invoices to orders? (How many invoices can an order have?) How do you keep track of how much of each order has been fulfilled?

How do you keep track mentally of which orders you can fulfill with the 6 parts? How do you translate that into the database? Do you ever get into mucking around with "oh, but I can ship all of orders 1001 and 1003 and just have 1002 on the books"? You're description says no - that's another of your rules relating to orders.

Are orders always for a single part? You don't have the part number in your example - presumably an oversight?

Note that it could be rather tricky to process this relationally; the easy way of doing it steps sequentially through the orders, fulfilling as much as possible, but that is not terribly much like a set operation.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/