Storing personal data on an invoice to preserve data integrity

Question

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

Answer 1

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.

Answer 2

Solution #1 is fine. Keeping an Addresses table is the normalized solution, and has the benefit of keeping your Customer and Invoice tables small. There probably isn't even a need to soft-delete addresses; if someone needs a customer's current address, they will look it up with Customer.AddressID. Your Addresses table can be read-only.

If you'd like to track when customers change addresses, independently of any invoices they've had, you can create a new CustomerAddressHistory table with fields CustomerID, AddressID, and DateEffective. You may also want an AuthorityID field (to reflect who made the change), and/or a DateObsolete field. A unique index on CustomerID WHERE DateObsolete IS NULL gives you instant access to a customer's current address, so if you'd like you could drop the AddressID field from the customer table.

The Invoices table would have an AddressID field. Your Addresses table would store all addresses ever used.

Answer 3

1. Reference Invoice with AddressInfoID as you are.
2. Add IsCurrent bit column to AddressInfo table.
3. When the customer changes address preserve original address record but change it's IsCurrent value to 0
4. Add a new AddressInfo record with IsCurrent = 1
5. Use AddressInfo WHERE IsCurrent=1 for all new invoices

Answer 4

I used to work in an organization with this problem and this is how we solved it. We had one database with the customer and service info. It had address fields that changed any time a customer requested a change.

The billing database was a separate entity and each month's invoice had the address it was sent to for that month and in theory i've never heard of it changing. I'm not 100% sure but I think the invoice address was generated monthly from one of the tables in the main database.

You have to be careful in changing addresses for invoices because of tax liability. Depending on the products you sell or services you provide your tax collection rates will differ sometimes on a county by county basis and you may be audited and have to provide this info in the future. Changing an address on a past invoice might get your company into trouble.

In reality we had more than two databases, but the bill creation process was extremely complicated in that case. We did have a separate database that held the invoices and other billing info with the table holding the invoice data under tight control and the SOX auditors would look at it every year.

We had raw customer service usage data in one database, pricing plans in another, customer and product lists/features in another database, a separate database for tax info that changed monthly and the taxes collected differed depending on the locality and all this came together to create a bill. Some customers had multiple locations so their taxes would need to be calculated per location.

Some customers paid different prices for the same service because they were bought at different times and different discounts and sale prices applied each time. Some bills were a few pages and some customers had bills in the dozens and hundreds of pages. And a few more databases for late payments, etc. Everything had to be accounted for and charged properly.

There was a main database that had the master customer names, numbers, addresses, products, etc. This was a telecom company and they have something called USOCS which is a crazy code for every product sold. Similar to medical billing codes. We also had a separate database for pricing plans. Think of it like the current cell phone plans, but we had thousands of plans because we had some large customers and some had custom negotiated deals.

One of the reasons it was so complicated is that the end devices that serve the customers have the raw usage data which had to be uploaded to our databases and then priced depending on the rates charged for that customer, their location, tax rates, etc. It's not like a physical product with inventory and the same price for everyone.

Answer 5

Why won't you go for System Versioned Temporal table (SQL Server 2016 onwards). I think it will address your requirement.

There are other vendors that support temporal tables as well. Both Oracle and IBM Db2 had this for 5-6 years now, they are also arguably more complete than SQL Server's implementation.

You can also implement a homegrown version in almost all DBMS, but some parts are trickier than others, overlaps for example. Performance can also be a bit of a challenge.

Answer 6

Why not simply use a mailed to address and tie it to the invoice. Then if the address doesn't change, no issue. If it does, you still have the address that particular invoice was mailed to.