Looking for some 'good practice' advice for the system I am currently trying to
develop. I have a table of JOBs (orders), with another table JOB_ITEMs
(order_items) holding each part of the job. i am now trying to set up the
invoicing part of the system but not sure how i best to do it. I am setting up a
new INVOICE table which holds the invoice number, job number, invoice date and amount paid. Is it a good idea to have an invoice_total field, or should it always be calculated from the relevant JOB tables whenever it is required (ie for account summaries and things).
Also, I have a customer table as well as the job and invoice tables. Is this enough to implement customer accounts? I suppose all information I need can be drawn from the tables. How would people go about implementing credit notes?
I would always store the invoice total in case the price changes after the order is billed. In fact you may wish to consider storing the price for each line when the order is created to ensure consitency.
Thanks, yes I already store each line of each order seperately in the JOB_ITEM table, so changes to prices will not affect the stored jobs. So It should therefore be safe to calculate the price of the invoice from the JOB tables each time they are needed?