Mysql – How to store historical records

database-designMySQL

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:

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.
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.

Related Solutions

MySQL table with 100,000 records queried often

You will have to compare the WHERE clauses and GROUP BY and ORDER BY statements of all your queries to make sure your current indexes can support them in their EXPLAIN plans.

Yesterday, I answered this question : InnoDB vs MyISAM with many indexes

In that question I suggested doing something to the MyISAM table that you can do as well

ALTER TABLE orders ROW_FORMAT=Fixed;

This will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. Your table will bloat to the maximum size for the row layout times the number of rows. You table could double or triple in size.

Where is the benefit? Your MyISAM table will then be read from/written to anywhere from 20% - 30% faster without changing anything else.

I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)

Mysql – Db design for historical option data (~200GB)

The best first strategy is always to keep your transactional data all together in one, normalized data store. This minimizes the risk of data integrity problems and keeps the amount of code you have to write to a minimum.

Often, people will find that there is some kind of concern around performance. This leads them to consider denormalization. The best thing is to wait until your volume testing proves that there is a real problem with performance, rather than assuming that there will be one because your table is "big".

Given that you have a large amount of historical data that you don't use most of the time, it is possible that you may benefit from denormalization. You really should test this out before assuming it to be the case though. If it is the case, then one approach which might work well for you is to keep two data stores: (a) one where your current values are kept and (b) one where all values, including current and historical are kept.

If this makes sense to do (for performance reasons), you should build some automation to track all changes to the current data and record them in your history table. This is best done with triggers in DBMS that support them.

What almost never makes sense is keeping current values in one place and historical values (excluding current values) in another place. I have never come across a situation where you need to query only historical records, excluding current values. This means that your queries would have to be twice as complex in order to use current + historical values if you segregate them in this way.

This is what I would recommend to you:

Keep you data in one place if you can (definitely index on date)
Denormalize if you have to, but keep current values in your history table.
Do not use a bit flag for "is_current". This almost never indexes well.

Best Answer

Related Solutions

MySQL table with 100,000 records queried often

Mysql – Db design for historical option data (~200GB)

Related Question