How to implement historical data with this EERD?

Question

I am looking for a way to implement historical data with the following EERD (what I have tried, below the image):

What have I tried?

• Temporal tables

Didn't work because I use MySQL and it has no native support. And also isn't the best solution (when doing natively) because then I am fixed to one database provider.

• History tables

This is a valid option but some things won't work. For example, I change the supplier name, a new record will be added to SupplierHistory table, and the data in the Supplier row will get mutated.

Then lets say for example the next day we change the address that supplier was/is linked to, address gets added into history table, row gets mutated...

But then the History Row of the updated supplier will still be pointing to the old address in the address table?

• isActive strategy

This is also a strategy, in which I set fields that are current and valid, to isActive true, otherwise false. There is also a problem with this one: my supplier and customer tables are used for login. So I cannot just add a new row for changed data, because it is linked to the Primary Key, ID, and all other tables reference to this. So I need to change the original row, but then there is data loss.

I don't know what to do/try anymore, doees anybody have suggestions?

Thanks in advance,

Kind Regards,

Answer 1

I like to start with two tables:

Current -- contains remaining inventory, counts, totals, etc. This is UPDATE-heavy and has one row per entity.

History -- Transactions -- INSERT-only; many rows per entity.

The app does SELECTs from whichever one is appropriate.

Current could be derived from History, but that would be costly.