Invoice tables Design

I want to know how to design invoice t开发者_运维问答able.

An invoice include many orders from order table.

Is this invoices table is correctly designed?

order

• order_id (PK)
• User_id (FK)
• total_cost
• order_date
• status

order_item

• order_item_id (PK)
• order_id (FK)
• item_name
• cost

invoice table

• invoice_id (PK)
• invoice_no
• invoice_date
• period_start_date
• period_end_date
• status

invoice_order (an invoice with many orders)

• invoice_order_id (PK)
• invoice_id (FK)
• order_id (FK)

Is invoice_order table necessarry?

I could add invoice_id (FK) field in the order_table instead. The "order. invoice_id" would be updated when I have added a row in the invoice table.

You only need the invoice_order table if:

• An order can have one or more invoices

AND

• An invoice can be linked to one or more orders

By your suggestion at the end of your question, that's the case. You should not just have invoice_id and get it updated when a new invoice comes in, because you would lose the link between the order and the previous invoice.

Update

By the way, it's good that you have cost and item_name in order items, which is something beginners tend to find weird. You have to have those for historical reasons and to make possible to reprint the order with the same data, say, 3 years later, when the item might have had its name slightly changed and cost has surely been updated.

You need the linking table. An order can be in mulitple invoices (if they didn't pay it!) and an invoice can contain many orders. In a linking table though I would not bother with •invoice_order_id (PK). The PK is a combination of the two FK fields. That guarantees uniqueness and since you are unlikely to have a child table of the link table, you really gain nothing by adding a surrogate key to it. Even if you did the performance difference between joining on two indexed int fields vice one would probably be negligible.

Most invoices will have:

• Customer ID
• Sales Rep ID
• Payment Method
• Ship to Address
• Billing Address
• CheckBox: Shipto same as billing
• etc.

Generalisation !! You should consider reducing that to 2 tables: Documents and DocDetails. Just add a DocType field in the Documents table, to differentiate Orders from Invoices.
If you need to track backorders, add a Link field to your DocDetails.
When you add an Order detail line, give the Link field the value of the table PK (counter).
When you add an invoice detail line, give the link the same value as the related order detail.

By the way, did not see any CustomerId in your tables !