Like you say it is not necessary. The order table could have a field invoice_id which would record the id of the invoice that that order relates to.

In your invoice table you have a field invoice_id (PK) and a field invoice_no. Could you get rid of invoice_id and have invoice_no as the primary key?

I think I will have invoice table table, just incase if I need multiple invoice for same orders.

On thing came in to my mind, shall I add total_cost field in the invoice table? (It calculate the total costs of all orders that is related to). But one day I might change the price in the order table or disable of the order - that mean I will have to update total costs field everytime (if invoice row exist)....

I think I will have invoice table table, just incase if I need multiple invoice for same orders.

Do you think that will ever happen / has it ever happened before? If the chances are high you might indeed do this, but if it's like "well maybe it may happen someday, maybe" and build it "just in case" I wouldn't. I don't think I've ever had any benefit for things I built "just in case" and in your example it does make thinks quite a bit more complicated. Besides, having a duplicate order and a simpler database beats having that extra table there that may be more semantically correct.

PowerStrike said:

On thing came in to my mind, shall I add total_cost field in the invoice table? (It calculate the total costs of all orders that is related to). But one day I might change the price in the order table or disable of the order - that mean I will have to update total costs field everytime (if invoice row exist)....

It depends on what you want to do with the data. If you need to show the totals of the invoices a lot then it would help in performance if you add an extra column to the invoice table. However, keep in mind that you are breaking database normalisation. Normally that's not a good thing but there are cases --like this one-- where doing it anyway helps performance so much it's worth breaking normalisation. Just really keep in mind you're doing it, and make sure you keep the total value up to date at all times, and keep in mind that this new totals fields is never leading; if the sum of the parts and the total are different, the sum of the parts is the correct value. This might seem obvious, but I doesn't hurt to repeat stuff like this every once in a while

Do you think that will ever happen / has it ever happened before? If the chances are high you might indeed do this, but if it's like "well maybe it may happen someday, maybe" and build it "just in case" I wouldn't. I don't think I've ever had any benefit for things I built "just in case" and in your example it does make thinks quite a bit more complicated. Besides, having a duplicate order and a simpler database beats having that extra table there that may be more semantically correct.

I will definitely need a design that an invoice can be linked to one or more orders.

I think you have made a good point... so basically to keep it simple I only need to add invoice_id field in the tbl_order table without having invoice_order table?

I want to generate an invoices for every 2 weeks automatically, I could use Cron Jobs for that?Here what I am trying to do... When customer placed an order, their order are stored in the order table. I want to create an invoice for period: 1 to 15 May from order.shop_id = 2 (An invoice will have multiple order_id).

I think you have made a good point... so basically to keep it simple I only need to add invoice_id field in the tbl_order table without having invoice_order table?

Yes.

PowerStrike said:

Note: invoice_id field will get updated if I insert a row in the invoice table.

Can't you create the invoice first so you can just set the invoice_id for the order when you create them?It seems a bit weird to first create the orders, then create the invoice, and the update all the orders to set the invoice id (plus it's more queries too).

PowerStrike said:

I want to generate an invoices for every 2 weeks automatically, I could use Cron Jobs for that?

Sure. What kind of products are you selling that need to create invoices every two weeks if I might ask?

Can't you create the invoice first so you can just set the invoice_id for the order when you create them?

The invoice is for shop owner to view (not the customer that orders). I will send the invoice every 2 weeks to the shops so they can see what the customers have order and what the commision fees I will get.

You say create the invoice first, how is that possible? I dont want every single invoice of each order.

The standard invoice date of every month will be: 01 and 15 for all shops.

Let say today is 05 September.. UserA placed the order from ShopB and he is the first customer of September but there is no invoice Date of 01 Sept in the invoice table, what is the solution to this? Use PHP code to check the current month and insert into invoice table if invoice date 01 Sept not exist?

That is why I thought to use Cron Job to scan in order table every 2 weeks and then create some rows in the invoice table then update the invoice_id field in the order table that are related to. (Eg: generate invoice between 01 - 15 September on order.shop_id = 2)

What's wrong with letting the orders build up over the two-week period and then, when you want to create an invoice you do so by clicking a button. That 'click' shuold insert a new record to the invoices tbale and get its last_insert_id at which time it should insert it into the orders table as appropriate, using an UPDATE statement?

Multiple orders in an invoice is for the shop also, so the shop can see how how many orders have received.

That won't be an invoice.It's a customer account.

You said clicking a button to create an invoice but imagine if you have over 2000 orders every 2 weeks.. You don't want to click on the generate invoice button for each shop.

Its been a while so what I expect I meant was, you could have the orders stored in the db and when you click a button, to create a new record which stores the order_id and the customer_id and the dat of order in a nother table. - a many-to-many table. then when you want to view an invoice, or a statement, it's an easy query for each task.

As mhack122 suggests, it might be an idea to see what the client wants as what you build is down to their tried and proven knowledge of their business.