Invoice lines and invoices: how do you store customer address information?

Question

Hi, I am developing a billing application.

So, the general idea is to have two tables:

Account (identifier, date, customer address, CustomerState, CustomerCountry, VAT, Total); InvoiceLine (Invoice_ID, ID, Concept, Units, PricePerUnit, Total);

As you can see, this basic design leads to a large number of repetitions of records in which the client will have the same settings, state and country.

So an alternative is to have an address table, and then create an Address <-Invoice relationship.

However, I believe that the invoice is an immutable document and should be kept as it was first made. Sometimes customers change their addresses or states, and if they come from an address directory that will change all previously made invoices.

So what is your experience?

How is the customer address stored on the invoice? In the invoice table? address table? or something else?

Can you provide pointers to a book, article or document where this is discussed in more detail?

Answer 1

I would highly recommend not storing any customer data like this on the invoice.

Instead, I would have a structure like:

Clients table with id primary key

Client address table (since each client can have different addresses over time), with the client identifier as a foreign key

A table of accounts, with an address field that is a foreign key in the customer address table.

By the way, I would think about adding a VAT field for each item. There are countries in which there are different VAT rates for different types of goods.

Answer 2

In most standard databases of goods / orders will be

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 table of the relationships of many factors between orders and products.

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

To get the full invoice, you will request an order table and attach it to the OrderItems table. OrderItem usually has a purchase price and such, because the price in the product table may change after creating the order and this information is often useful for storage.

Answer 3

I would think of doing this with three tables: Customer , Invoice and Address , but build it so that after entering the address it will never be updated or deleted, but will only become outdated. In the address table, you can have the IsDeprecated or IsActive boolean field. Then, when you create the invoice, the invoice refers to the customer identifier and the address identifier that is being used at that time. When the client changes its address, you create a new record with a new AddressID and denounce the old one with a logical field. Or, if you really want to keep good records and / or ever need to find this data, you can have AddressActiveStartDate and AddressActiveEndDate , but that will make queries a little more complicated.

This way you keep the old address, while still linking to the client for reference, it also allows the client to have more than one specified address (for example, one for sending, one for billing).

If necessary, you can add more tables, for example. Product , InvoiceLine , State , etc.