How to store historical records?

Question

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?

Answer 1

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.

Answer 2

There is a third option which is a combination of the two you've already thought of. You could keep current records in one table and all records in another table (current and historical).

In this approach, you can pick which table to read based on which type of question you have. If you only care about current values go to the current table. If you care about history, presumably your interest in history includes current values. Therefore you have "one stop shopping" for whatever you view you need for each circumstance.