1

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.

3
  • ones that I have missed? Triggers, perhaps?
    – mustaccio
    Aug 10 at 1:28
  • @mustacio Ok we can add that option but which do you think would be the best option? Aug 10 at 1:44
  • Please consider posting TEXT results of A) SHOW CREATE TABLE user_profile; and B) SHOW TABLE STATUS WHERE name LIKE 'user_profile'; for analysis. We may see some data that might permit getting you to less than 15M rows in this important table. Aug 10 at 14:25

2 Answers 2

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.