Storing personal data on an invoice to preserve data integrity
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.
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.
- Reference Invoice with AddressInfoID as you are.
- Add IsCurrent bit column to AddressInfo table.
- When the customer changes address preserve original address record but change it's IsCurrent value to 0
- Add a new AddressInfo record with IsCurrent = 1
- Use AddressInfo WHERE IsCurrent=1 for all new invoices