Invoicing

[John thomas]

Hi all
Needs some help with design .
I have a fully functioning orders database ,and it works really well ,Now to complete things I need to add an invoicing method .and a way to keep track of payments.
I have produced an invoice wich is a report based on a query ,however the more i think about it the more problems come to mind .For example .If the customer changes there address details ,and sometime in the future I wnat to pull up there original invoice .Becouse the report is based on a query it will put the latest customer details into the repot /Invoice .This would be a serious error .
The cost of products is ok as I am storing those in a table So they will not change .However I am not storing calculated fields ,I do not think this will be a problem in the Invoice .But wonder how i can keep track of deposits and total payments in I presume a different Table as these calculated fields are not saved anywere .All the information I can find says dont store calculated fields but use querys to work them out
I am guesing i need an
Invoice Table
A table to keep note of payments
and a table that lists the diffrent percentage of deposits
One last thing ,The invoices will be printed and given to the Customer so when I Bring up there invoices in the future they must be Identical to the Printout
Many Thanks in advance

 

[CJ_London]

Invoicing is the one of the few situations where you would store calculated fields and/or duplicate data.

With regards addresses you would typically have addresses in a separate table from the customer with a one to many relationship. Then you can have invoice addresses different to delivery addresses etc. If an address changes then you simply add a new one so previous invoices will still link back to the correct one. The only time this falls down is if there is a typo in the address which is corrected - so previous invoices will then show the corrected address. But if you want to keep it exactly as is, then you will need to store a copy of the address within the invoice data, or prevent correction of typo's - i.e. the user will need to enter the correct address completely.

The same can be said for things like product descriptions - So long as your business rules are clear about what can be allowed to be 'corrected' and what can't you should be OK.

It is a good idea to store within the invoice detail the quantity ordered, the price and related taxes and rates so that the calculation can be duplicated at any point in the future.

Also I usually store in the invoice header the calculated values for invoice value, total tax etc for two reasons 1) with high volumes of invoices reporting performance can be slower if the invoice total needs to be calculated from the detail and 2) it is a useful check to reconcile that you have all the invoice detail records.

With regards deposits - keep these in a separate table which as a minimum will have an invoice number, the date of the deposit and the amount.

 

[John thomas]

Hi cj
Many thanks ,that helps a lot