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:
- MySql Event scheduler
- Cron Job
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.