Mysql – How to store historical records


What's the best practice approach to storing historical records?

I have an entity whose rows will constantly get updated. I need to keep track of each row's history. There will be about 40K "current" records at any given time and each of them will be updated on a daily basis for about 60 days, then the updates will stop.

I see two options here:

  1. All current and historical records in one table – since each row will have an id that will allow me to identify which records are part of the same group.

  2. Current and historical records in separate tables – each time the current record is updated, a copy of it pre-update is put into the history table. The history table would have a FK.

Is there another option I haven't considered? Which option is the more appropriate approach to what I am trying to do?

Best Answer

There are some Open Source Change Data Capture tools that support MySQL you can find some of them in this answer: Does MySQL have a version of Change Data Capture?

I would personally recommend option #2 if you choose to deal with it yourself. A few simple triggers can automatically copy the new/modified rows into the other table and you can add a few columns in the history table to help track what is going on without having to touch any of the queries that use the primary table. This solution is extensible and is easy to deal with from a querying point of view.