6

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?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
elviejo79
  • 4,592
  • 2
  • 32
  • 35

3 Answers3

8

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.

Uri
  • 88,451
  • 51
  • 221
  • 321
  • 1
    Don't you think an invoice table is almost like historical table and therefore duplication of information on how the invoice was originally printed is valid? – elviejo79 Jun 02 '10 at 00:43
  • 2
    @elviejo, arguably you are correct with that, but you have to evaluate whether you want to store the same info over and over, or whether you just store a foriegn key to the address that was used. If you store a foriegn key then it should mean that you cannot change old addresses once an invoice has been linked to it, instead any changes would have to become a new address record and you just maintain a flag on the address table which indicates that the address is active or not. – slugster Jun 02 '10 at 00:58
  • 1
    @elviejo You can also do this without having to alter the whole address maintenance process for your customers. At the time an order becomes an invoice, you can link to a customer entity AND an "anonymous" address entity (perhaps in both a bill-to and ship-to roles) which simply contains the address at the time of the invoice. You look up on all the address columns and get a surrogate key. This "address" history is never updated, only inserted. And previous addresses are simply re-used. Thus you get point-in-time data without having to alter your entire customer maintenance design. – Cade Roux Jun 02 '10 at 02:00
  • @elviejo: From a legal point of view, duplication is clearly best since you can't "retroeactively" change data accidentally. In fact, paper copies are even better. – Uri Jun 02 '10 at 02:26
  • 1
    @uri in this question: http://stackoverflow.com/questions/316757/uk-vat-change-from-17-5-to-15-how-will-this-affect-your-code/316962#316962 They make the point that storing calculated VAT is good practice. And I think the address would fail in the same category. maybe there is no definitive answer to this question? – elviejo79 Jun 04 '10 at 20:00
  • 1
    @elviejo: I think you misunderstood me about the VAT. My point was that VAT should be at the level of the line item, not just at the level of the entire invoice. For instance, in my state (PA) we pay a different tax level on each item we buy at the store. Meanwhile, at a restaurant, we pay a different tax to the state (for the food), and a different tax to the city (for alcohol). My point is that for better traceability/accounting, you may want to do taxes at a line item level rather than a whole invoice. For instance, if someone returns an item, you need to reimburse them whatthat they paid. – Uri Jun 04 '10 at 21:20
  • Why do you have a foreign key to a customer address table in the Invoice table and not to the Customer table? I mean that Invoice table and Customer address table have the foreign key ref to the same primary key in the Customer table. I'am still learning. – Xtreme Jan 25 '21 at 18:12
2

Most standard product/order databases will have 

a products table (ProductId, product info fields)  
a customers table (CustomerID, customer info like address etc) 
and an orders table  (OrderNumber, CustomerID, date, etc)

Then your order items become a many-many relationship table between orders and products. 

orderItems (OrderNumber, ProductID, quantity, purchasePrice, vat, etc)

To get a full invoice you'd query the orders table and join it with the OrderItems table. OrderItem usually has purchase price and such because the price in the product table may change after the order is created and that information is often useful to store.

Josh Sterling
  • 838
  • 7
  • 12
  • 1
    while I agree with your general design, I have been criticized in the past for the fact that orderItems does not follow normalization, in the sense that you will have thousands or millions (Billions?) of the same pieces of information in the productID, price, etc. How do you account for this or is this an example where normalization fails? – JM4 Jul 18 '12 at 22:23
1

I'd consider doing it with three tables: Customer, Invoice and Address, but construct it so that once an address is entered, it is never updated or deleted, only deprecated. You can have an IsDeprecated or IsActive boolean field in your address table. Then when you create an invoice, the invoice links to the CustomerID AND to the AddressID being used at the time. When the customer changes their address, you create a new record with a new AddressID and deprecate the old one with the boolean field. Or if you really want to keep good records and/or will ever need to look up this data, you could have an AddressActiveStartDate and AddressActiveEndDate, but this will make queries a little more complicated.

That way you still store the old address, with it still being linked to the customer for reference, while also allowing the customer to have more than one listed address (e.g. one for shipping, one for billing).

You can add more tables as necessary, e.g. Product, InvoiceLine, State, etc.

WhatEvil
  • 481
  • 5
  • 18