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
I have two solutions for this, but I'm not sure which direction is the right way to go:
AddressInfoand make it soft-deletable, adding some column like
Invoicetable 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.
Invoicetable would look somewhat like:
| InvoiceId | AddressInfoId |
Add needed columns from
Invoicetable. 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.
Invoicetable 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?