Hi I'm developing an invoicing application.
So the general idea is to have two tables:
Invoice (ID, Date, CustomerAddress, CustomerState, CustomerCountry, VAT, Total);
InvoiceLine (Invoice_ID, ID, Concept, Units, PricePerUnit, Total);
As you can see this basic design leads to a lot of repetiton of records where the client will have the same addrres, state and country.
So the alternative is to have an address table and then make a relationship Address<-Invoice.
However I think that an invoice is immutable document and should be stored just the way it was first made. Sometimes customers change their addresses, or states and if it was coming from an Address catalog that will change all the previously made invoices.
So What is your experience?
How is the customer address stored in an invoice? In the Invoice table? an Address Table? or something else?
Can you provide pointers to a book, article or document where this is discussed in further detail?
I would strongly recommend against storing any customer details like that in the Invoice.
Instead, I would have a structure like:
Customer table, with a primary key of id
Customer address table (as each customer may have different addresses over time), with the customer id as a foreign key
Invoice table, with an address field that is a foreign key to a customer address table.
BTW, I would consider adding a VAT field per line item. There are countries where there are different VAT rates for different item types.