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)…
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.
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
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).
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).
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)
The Invoice will look something like this:
Invoice No: 1234
Company Name
Order ID | Order Date | Amount
34 10/01/2011 £1.00
20 13/01/2011 £1.50
44 13/01/2011 £5.50
Sure. What kind of products are you selling that need to create invoices every two weeks if I might ask?
I will be selling food from online. There will be list of of shops from my website, customer can choose a shop/company and then select food to order.
order for shop x, item y:
if (there is invoice for shop x) {
invoiceNumber=number(invoice)
} else {
invoiceNumber=new Invoice(shop x)
}
new Order(shop x, item y)
That way you don’t need a cron job at all.
My rule of thumb is that if you can work around using a cron job, do it. Only use it if you really really have to.
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?
I suppose its worth adding that if you are in the UK, there should be an invoice per order! I may not have understood that you meant ‘statement’.
for the benefit of others:
When you place an order for one or more items, an order invoice/docket, is created.
Then at the end of the month, or whenever, the document sent out for payement is a statement - a composite invoice if you like - which itemises each item ordered and which invoice it relates to.
There will be an invoice per order for the customer
Multiple orders in an invoice is for the shop also, so the shop can see how how many orders have received.
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.
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.
