Re: Database Design Question

Jonathan

    Thank you very much for your reply. I did not consider that my post was way off topic, but of course I am prejudiced, and like all posters who pose a question, I am relying on the good nature of all the others in the ng to consider my problem, and post a reply.

At the moment we don't have a facility for processing remainder orders i.e. an order comes in, we supply whatever we can from it and basically forget about the rest. (Not very good) this is why I am trying to come up with a system to handle them. Because it is a brand new addition to our systems my thinking is probably fuzzy and unclear.

An invoice is generated from user input according to the goods supplied. This makes a recordset (for want of a better description) which I want to compare with any orders that may be there for this customer. I want a report printed automatically after every invoice which lists any items from which particular order are being fulfilled by this invoice, and then a list of remaining balance orders.
You ask about business rules.
1 Orders are to be fulfilled according to date placed by that customer. e.g. order 1001 must be fulfilled before we look at order 1002 2 An order may be fulfilled on multiple invoices 3 There will be many items on an invoice for which we have no order (We are a Cash & Carry so most of the goods get invoiced because the customer selects them while on a visit to our premises)

You ask "Are orders for a single part?". If you mean part = Item Number then no, a customer order will consist of many items. I was trying to show the problem I have in reconciling the invoice quantity with orders for a particular item / part.

I don't understand what you mean by "Set operation"

Regards

Eoin Fitzpatrick

"Jonathan Leffler" <jleffler_at_earthlink.net> wrote in message news:3EDACD65.80809_at_earthlink.net...
> 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/
>
Received on Mon Jun 02 2003 - 16:42:26 CEST