Database Design – Storing Personal Data on Invoices

database-designdenormalizationschema

I want to store invoice data like addresses of people. But the problem is addresses can change over time – for example someone will move out and change his address. The problem of storing such data arises, because we can't change the data that is referenced on some invoice.

I have 3 tables: AddressInfo (contains StreetAddress and so on), Person which has one-to-many relation with AddressInfo, and Invoice table.

I have two solutions for this, but I'm not sure which direction is the right way to go:

  • Create AddressInfo and make it soft-deletable, adding some column like IsActive or IsDeleted so my Invoice table can reference this address info by it's id and it's guaranteed to remain the same unless someone directly changes this data without using my application.

    So when someone changes his address, I soft-delete (perhaps it should be called soft-update?) the current address and add new one without touching old record at all. It will be probably good to check if it's referenced on some invoice first to prevent pollution.

    My Invoice table would look somewhat like:

    | InvoiceId | AddressInfoId |

  • Add needed columns from AddressInfo to my Invoice table. I will have to copy the data each time I create new Invoice, addresses don't change frequently that would pollute my database a little bit with duplicated records.

    My Invoice table would look somewhat like (greatly simplified):

    | InvoiceId | StreetAddress |

Do you have any experience with databases(perhaps of some ERP software) that solve this particular problem?

Interesting reads:

https://martinfowler.com/eaaDev/timeNarrative.html

Best Answer

The solution I have seen is copying address data to the InvoiceHeader table. This is redundancy, but it is not harmful*. Update anomalies do not occur, because the address on the invoice should not be changed later.

An analogous point could be made about copying product price data into the InvoiceDetail table.

* Update anomales only occur for harmful redundancy. Still, if the customer address changes in the customer table at a later point in time, but remains unchanged in the InvoiceHeader table, then they will no longer be redundant.

Related Question