Database Design Question (1 Viewer)

[ions]

According to database theory you should never store the same data twice. I want to present 1 scenario and explain my undertanding of design and I hope then someone can point out my errors if there are any.
*********************************************************
Current Scenario: Suppose you have

OrderDetails Table is related (Referential integrity and both cascades) to the OrderHeader table and has the following fields.

LineID, OrderNum, InvoiceNum, Description, NumberUnits, UnitPrice, WasteClass,

..... and suppose you have

InvoiceDetails table which has the same fields as above but is related to the InvoiceHeader table (RE and Both Cascades)

This design is no good because InvoiceDetails stores the same info as OrdersDetails.

To solve this

1. Get rid of the the InvoiceDetails table and create a relationship between InvoiceHeader and OrderDetails with the InvoiceNum fields. This relationship can only have Referential Integrity and Update Cascades but No delete Cascades.

2. The original relationship between Orderheader and OrderDetails remains the same.

3. When we delete an Invoice we must programatically set the InvoiceNumber field in the OrdersDetail records to Null.

4. When an OrderDetail has an invoice Number associated with it we must lock it from further changes in the Orders Form.

5. The OrderDetail can be unlocked for changes in the Invoice form.

Is this good design? Any errors or suggestions. Seems a bit odd to design this way at first but I can see the power in this. This design completely eliminates updates between the InvoiceDetails and OrderDetails tables.
Also it saves tons of space cause the InvoiceDetails table is gone.

I currently have a much more difficult scenario and I can't figure out how to normalize it but I want to make sure the above is correct.

Thanks.

 

[Ron_dK]

I would suggest to base any invoice on a query made from the order tables ( Orderdetails and Orderheader). In a business model, an invoice can only be created when an order is completed. So all relevant data for the invoice should come from the order and order related tables.

In Access, there is a wizard to create an Order Entry Database. Take a look at that one and check how the relationships are built.
This Dbase gives a good insight on basing invoices on the order table, rather than creating specific Invoice tables.

Hth

 

[stopher]

Are you absolutely sure that the invoice detail will always be the same as the order detail? So you never have a line out of stock or a customer cancels a single line on an order?

If you are sure then why not just incorporate all the invoice header info into the order header ? The order number remains a primary key and the invoice number is set to no duplicates (the only question here is how you generate new invoice numbers that have never been used).

Even if you don't do this then you should link invoice header to order header and not to order detail. In your orders form you simply check if the order header has an invoice number and if so, lock the order detail.

How do you intend to cancel an invoice. Are you literally deleting the record? Or are you flagging it as cancelled ?

Stopher

 

[ions]

Based on what you both described it sounds like you have to invoice each order seperately. My client wants to invoice multiple orders (same customer) on one invoice. I forgot to mention this sorry. Hence, I believe you do need a Invoice Header for this.

If a customer cancels a single line in an order they just delete it. This is not a problem.
You can cancel invoices by just deleting them. This is solved by making InvoiceNum Null for the orderdetail lines associated with the deleted Invoice.

(Note I forgot to mention this step in the original post.
When an invoice is created place the invoice number in the InvoiceNum field for every OrderDetails record in that invoice.)

Is this the most effecient design?

 

[gemma-the-husky]

Sorry about the following - it started as a few brief thoughts, and then gradually expanded. I hope you find some of the thoughts relevant.


In any normal system, such as you describe, I am sure you would NOT want to directly base an invoice on an order. An invoice could even relate to more than one order. An order could result in multiple deliveries, partial deliveries, non deliveries. The order date and delivery date(s) will be different etc.

Therefore you probable need at least the following entities:

order headers, order items, to manage the orders

delivery headers, delivery items (linked to the orders files) to manage actual deliveries.

invoice headers, invoice lines, to manage the invoices.

The invoices are bound to be directly linked to the delivery ticket file, rather than the order file.

You definitely need an invoice file. You only need to store the invoice number on the delivery file, and invoice related info, such as tax date, invoice totals, vat/tax totals etc on the invoice record, as well as payment data, and possibly other sales ledger type data.

If this is a new system design you have to be sure you understand your enterprise rules very precisely. eg the above scenario deals with orders, but you may also deal with instant sales such as cash/counter sales. An order may be open for an extended period and may be subject to price variations, so you may need a price history attached to the order file, and so on. You have to consider at what point unfulfilled orders are cancelled, or do you raise back orders (purchase orders) to fill the missing quantities.

Because the order/delivery/invoice are different entities it is certainly not an error to store 3 different files.

This does not even touch at all on credit notes - how do you process returns, price and delivery errors and damaged deliveries.

Also consider - an order may be for a particular part. so in theory you would only store the part description once in the parts file, so that the data is normalised properly. However if that part is ever renamed - say a 3/4 inch screw is renamed as an 18mm screw, or say Stainless Steel is renamed to a specific grade of Stainless Steel, then by inference old orders/invoices (if re-examined) etc would now carry a different description from that originally used. Therefore in some circumstances it MAY be necessary to introduce what appears to be unnormalised data by actually storing the original description. The alternative is to have an enterprise rule that prevents parts being renamed, but this is most probably unrealistic.

At some point you would probably find it impossible and impractical to track every possible change to your system files, so you will have to have compromises somewhere along the way.

Consideration of all these issues is not at all a trivial exercise for any but the most simple situations, and the implications and effects for a particular enterprise or system need to be fully understood and agreed at the outset, which is why properly specifying systems is so important.