Question about ERD

[quanzi_1507]

Hi, I have to design an ERD based on these order and delivery note samples.

Order sample
-Order ID (random)
-Customer's name
-Customer's address
-Date of order
-table (list of item ordered)
ID (ordered number, random) | Item's name | Description | Unit | Quantity

Delivery note sample
-Delivery note ID (random)
-Customer's name
-Customer's address
-Place of delivery
-Date of delivery
-table (list of item delivered)
ID (ordered number, random) | Item's name | Unit | Unit Price | Quantity | Total Price per Item

So far I've created an ERD as the one attached. However I notice that each order will have its own delivery note (with the same customer's name, customer's address, item's name, quantity) so there must be some kind of relationship between them. How should I create a relationship between these two entities then?

[urtrivedi]

I think keep structure of delievery as it is. You may add one column in delivery that is order_id_reference. while creating delivery note you can show list of order and could be selected, which will copy all details of customerid, address and all that. You may also bring items and ordered quantity.

Here user may add/edit/modify details (delivery may not look same as order)

[quanzi_1507]

Thanks for the quick reply. So even if the order_id_reference doesn't show up in the actual printed delivery note, I can add a order_id_reference in delivery structure?

Another question is: should I keep the "total price per item" in the delivery note structure? (since it can be automatically calculated based on item's unit price and quantity).

[urtrivedi]

If you are doing for class project than its fine. You do not need to show order_id_reference. It may be used to track orders if required.

But in real case one delivery may consists of many orders. So in that case you will need order, delivery relation table, that is one delivery many orders.

You should keep total column, and store calculated values in it, it means we are showing in report what ever stored in database. Always avoid calculations in reports (this is my personal opinion).

[quanzi_1507]

Again thanks for the clear explanation. I've never thought that a delivery could carry with it items from multiple orders, but since this is a simple class project I want to keep it as simple as possible.

So if I were to convert diagram above to this format (not sure how you call it).

Customer (Customer's ID, Name, Address)
Item (Item's ID, Item Name, Description, Unit, Unit Price)
Order (Order ID, Date, Customer's ID)
Order detail (Order ID, Item ID, Quantity)
Delivery Note (Delvr. note ID, Date, Place, Customer's ID)
Delivery detail (Delvr. note ID, Item ID, Quantity, Total price per item)

Does that look fine? Any suggestion on how to improve?

[urtrivedi]

1) also keep address part in deleiver and order, because all customers are not regular customer, may want delivery at another address

2 ) you may keep amount column in deliver(qty*price)
3) as discussed in previous posts, you may add order_id_reference in delelivery_note

[quanzi_1507]

1) Not sure if I made this clear but there are already place of delivery (the address the customer want to delivery at, might be or might not be his own address). The customer's address is just there for the company to contact him/her to confirm the order for example. And from the Customer's ID in order and delivery we could figure out his address, so I think adding address in order and delivery is not necessary, right?

2) The quantity*unit price is already included in the Delivery detail (Total price per item)? Or are you talking about the total amount (= sum amount of all item) the customer must pay when all the items are delivered? If that is the case the total amount is not mentioned in the delivery sample (even in real life it should be).

3) Agree.

[urtrivedi]

1) look the sequence
   customer is at A
   he placed order 1
   order 1 delivered to place A
   customer changed address (you update master table) A->B
   he place order 2
   order 2 delivered to place B
   now you look for order 1 in reports
   it will show order was delivered to address B (is it true?)

2) This is delivery so you do not need to keep amount related things, only quantity is enough. 
But in case of invoice, You must store and show qty, rate, discount, total_amout item wise.

[quanzi_1507]

I understand what you said earlier now, thanks.