ansaurus

Question

Invoice & Invoice lines: How do you store customer address information?

Answer 1

+3 A:

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 2010-06-02 00:23:55

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?

elviejo 2010-06-02 00:43:40

@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 2010-06-02 00:58:42

@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 2010-06-02 02:00:26

@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 2010-06-02 02:26:57

@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?

elviejo 2010-06-04 20:00:50

@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 2010-06-04 21:20:24

@Uri. Got it. thanks

elviejo 2010-06-04 23:15:25

Answer 2

A:

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 2010-06-02 00:31:03

ansaurus

tags:

views:

answers:

Invoice & Invoice lines: How do you store customer address information?

related questions