Invoice tables Design

0
votes

I want to know how to design invoice table.

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.

4
mysqlsqldatabasedatabase-design
possible duplicate of Invoice from multiple orders? - ypercubeᵀᴹ
I see too many invoice design questions the last days ... - ypercubeᵀᴹ
@ypercube, It would save time if those darn instructors would post the question once, on behalf of all their students. - Philip Kelley
There's very little sense in building a system like this for real world use. I guess your purpose is purely educational. In that case I suggest you decide what you want to achieve and come up with a better spec for your hypothetical system. - nvogel
@dportas Are you saying bad designed for real world use? My question was how to design tables for invoice can be linked to one or more orders. - I'll-Be-Back

4 Answers

3
votes

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.

1
votes

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.

1
votes

Most invoices will have:

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

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 !