MySQL - archiving data in tables

Question

I am a developer for an insurance app. We are using AWS RDS with a MySQL implementation. The scenario we would want is to automatically archive data from one table to another. Something like this:

Let's say you have a table called user_profile. You want to record a history of the changes of each user profile in another table, let's say we call it user_profile_history. Is it possible in RDS to do real time porting from the main user_profile table to its history table, whenever updates are done to the main table?

End scenario would be, user_profile table only contain the latest user data. All other past snapshots of profile are in the history table.

I did my due diligence and did a little bit of research and there are possible options to do this:

1. MySql Event scheduler
2. Cron Job
3. Partitioning

My question is, which do you think would be best suited specifically for this scenario? or are there other better ones that I have missed? Currently records in the main table are in the millions (15M approximately) with about 5k records added each day. That is why we decided to have a separate history/archive table to it, to be able to transfer some of the old stale data to a separate table.

Answer 1

I think triggers is your best option as mustaccio mentioned. There are multiple options:

Create a BEFORE INSERT Trigger Create an AFTER INSERT Trigger Create a BEFORE UPDATE Trigger Create an AFTER UPDATE Trigger Create a BEFORE DELETE Trigger Create an AFTER DELETE Trigger

Select the one you want, or all :D, you can control it all.

See also this tutorial: https://www.mysqltutorial.org/mysql-triggers

Answer 2

I prefer to do it when initially storing the data. Two tables:

History contains all the data, including what was just now received. Example: all your bank statements on a single list.

Current contains the information as of now. Example: your current balance.

The History contains all the data necessary to reconstruct what is in Current.