Re: Database Design Question

Eoin Fitzpatrick wrote:
> 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.

My questions were intended to provoke thought to help you clarify your requirements - it is extremely difficult for an outsider such as myself to provide an answer because we don't know all the information (rules) that need to be applied. And by the time you know all those rules, you don't need the outside help.

> 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're a cash'n'carry outfit, but you also accept orders from a customer which you then make up (but currently incompletely when you don't have enough of some item). The invoice that is generated is taking the information from a cash register or equivalent as the operator scans items - or something along those general lines? Does the customer still come to collect the order, and you then deal with the missing stock? Since you've still not really explained how an order is created, nor how an invoice is created, nor how (or whether) the invoicing operation creates an order when there is no order outstanding, and so on, it is hard to work out exactly what you're up to.

At a rough guess, for each (item, quantity) pair on an invoice, you are going to need to find the orders for the current customer which contain a request for some of the item, and then deduce whether the order is completely (exactly) fulfilled, under-fulfilled, or over-fulfilled. If it is under-fulfilled, you are going to have to record somewhere how many of the part have been supplied (possibly with the invoice number) and therefore how many are still outstanding.   If the order is exactly fulfilled, it is nice and easy. If it is over-fulfilled, you need to try and find another order also requesting the same item, and deal with that one too - ad finitum. If there are still items being invoiced for which there is no order, you have to decide what to do - create a new order or extend an existing order or ignore it and simply invoice the unordered goods, or what.

You're going to need to decide on the appropriate structure(s) for your order, order items, invoice, and invoice items tables. In fact, you will need to think hard about the total number ordered vs the number shipped per invoice and how you deduce the number outstanding.

> 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 thought as much - and that's fine (though it helps to say "I've omitted the part number from the discussion").

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

Well, this is c.d.theory, and the majority of the discussion revolves around RDBMS and why they're better (or not) than xDBMS for other values of x. And RDBMS are based on set theory, and all operations on the database are supposed to be done using set operations. This is a mild attempt to provoke discussion of what is a slightly tricky operation to express when using just set operations and not tricks with cursors and the like.

> "Jonathan Leffler" <jleffler_at_earthlink.net> wrote:

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

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

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

>>>of them refer to order #1002 and the remaining 3 belong to order #1003.
>>

>>>also need to show that there are still 2 pieces outstanding from order
>>

>>>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/
>>

-- 
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/