Re: Database Design Question

From: Eoin Fitzpatrick <eoin_at_no.spam.fitzpatrickwholesale.ie>
Date: Tue, 3 Jun 2003 23:47:17 +0100
Message-ID: <bu9Da.16598$pK2.22831_at_news.indigo.ie>

Jonathan,

    Thank you very much for continuing to answer my posts. Sorry for not explaining correctly what I am having problems with.

snip >
You ask about how an Invoice are generated? The invoice is generated by the goods physically going through a checkout. This is the 'supreme' procedure and document because it is the physical goods being checked out. Everything else is subservient to this. (There will be many items invoiced for which there is no order, in this case I don't want to bother with an 'dummy' order)

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

You correctly deduce that when comparing the invoiced_Quantity with the (unfulfilled) Order_Quantity for 1 particular item/part, there are 3 possible results :-

1 Invoice_Qty = sum of all(Outstanding Order_Qty for this customer) :- everything balances out so no problem here 2 Invoice_Qty > sum of all(...Order_Qty...) :- Ignore the difference. There will be many items for which there is no order. We expect customers to select more goods (both in number of items, and in the quantity of each item they want, when they see our wonderful displays of product ;-) than they had on order.
3 Invoice_Qty < sum of all(...Order_Qty...) This is where I am having trouble deciding what to do.

Taking my original example (and the poor way I had thought of trying to work it out)
Order_Detail_table
Order_DetailID (PK AutoInc)
OrderID (FK to the Order_Header Table)
Item_Number (FK to the Products Table)
Quantity
Unallocated_Quantity
InvoiceID (FK to the Invoice Table)

Order_DetailID        OrderID         Item        Quantity
Unallocated_Quantity        InvoiceID
xx1                            1001            12345        1
1                                    0
xxx                            1002            12345        2
2                                    0
xxy                            1003            12345        5
5                                    0

This is the position before an invoice is generated.

The invoice #654321 is processed, with 6 of item 12345 on it.

The invoice quantity of 6 is applied against order #1001

the invoice (un-apportioned) quantity is reduced by 1 (the Unallocated_qty on the order). The unallocated_qty on the order is set to 0 meaning this order has been fulfilled, and the invoice number is put in InvoiceID

This record in the table will now read

Order_DetailID        OrderID         Item        Quantity
Unallocated_Quantity        InvoiceID
xx1                            1001            12345        1
0                                   654321

As the unallocated_qty is 0 and the InvoiceID > 0 this order is complete and can be 'forgotten' about.

Next the same process is done with Order # 1002

After this is done the Order_Detail_Table will be

Order_DetailID        OrderID         Item        Quantity
Unallocated_Quantity        InvoiceID
xx1                            1001            12345        1
0                                    654321
xxx                            1002            12345        2
0                                    654321
xxy                            1003            12345        5
5                                    0

The invoice_unapportioned Qty is now 3

so what do I do, when I try to apply 3 against the order quantity of 5? If I put the invoice number into InvoiceID, it will be overwritten the next time the customer receives more of item 12345

I sense that going back through old records in a table and updating (rewriting) them is not the relational way of doing things, but I cannot see how to do it! I know this table structure won't work, but I cannot get my feeble mind past this flat model!!

Your other questions are answered below

> 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?
The Physical goods are scanned through a checkout

>

 Does
> the customer still come to collect the order, and you then deal with
> the missing stock?
Yes, they do come and collect, this new system is to handle the goods ordered but not invoiced senario.

> nor how (or whether)
> the invoicing operation creates an order when there is no order
> outstanding, and so on,
If there is no order for the goods being invoiced, don't worry. Ignore this

>
> I thought as much - and that's fine (though it helps to say "I've
> omitted the part number from the discussion").
 sorry for not making this clear.

Thanks again for all your help

Eoin Fitzpatrick Received on Wed Jun 04 2003 - 00:47:17 CEST