MySQL Forums
Forum List  »  Newbie

How to implement historical data with this EERD?
Posted by: Jahnae Yina
Date: March 17, 2024 05:04AM

I am looking for a way to implement historical data with the following EERD:

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,

Options: ReplyQuote

Written By
How to implement historical data with this EERD?
March 17, 2024 05:04AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.