Category: Programming

MySQL Table History

MySQL Table with History

MySQL Table with History

It is a common requirement to have a history of changes to any screen.  In this post we will explore a simple and efficient mechanism to track history on a MySQL table.  We will be able to track any record back to birth, using MySQL only, no application server code, using the MySQL Table History technique.

The Primary (Original) table

For this example we will be using a table configuration table.  This table is used to configure a PHP application and augments the common static array used in many PHP frameworks to maintain the configuration.

This table is very basic with some common MySQL techniques in it, “is_deleted” field to track soft deletes, “changed_datetime” to track the date and time of the last insert/update, and optional tracking fields for who and where.

The History table

Ideally we would track the history of any changes to this table, by record from birth to death.

While it is obvious we can use triggers to do this, by making some subtitle adjustments to our history table we can reduce the effort and minimize the history tables impact on performance.

The first trick is how we layout the history table.  By coping the primary table’s structure and putting the history’s table index as the first column we can use “null, *” in our triggers, so we don’t have to specify every column by name.

Our history version of this table would look like:

Some special notes about the structure of the history table:

  1. The name is the same as the original table with “_hist” at the end.  This is means that history tables will show up right after our primary table in IDEs and SQL tools.  This is my personal preference, if you wish, you could group all the history tables together by pre-pending “hist_” to all the history tables.
  2. All the columns are in the exact same order and type as the primary table, with all “history” related columns at the beginning.  As you add columns to the primary table, you will need to add them in the same position in the history table.
  3. The only key or index is the history table’s primary key.  There are also no foreign table relationships in the history table.  The only indexing on this table should be the primary key.  This is for speed.  If you need sophisticated history searches you can add Indexes at the expense of update and delete speed.

While these are subtle they are important.

The triggers

Understanding the triggers

Each of the two triggers have essentially the same logic, the first trigger happens before UPDATE and the second happens before DELETE.

The trigger logic selects the existing record with the same primary key id and inserts it verbatim into the history table adding a null as the first column value.  This means that as long as the table structures match between the primary table and the history table the existing data will be captured in the history table.

Some testing scripts

After testing only “I am newest” should be in the ‘config’ table and all other records should be in the history table.

Conclusion

With this simple technique you can add history to you tables.  If you start your tables with this technique you can trace records back to birth.  There are some costs in terms of storage.  You will also take a small hit in speed but not as much as if you did the same thing in application logic, sending the same record over the network multiple times to record the history.

You can also extend this technique adding a time stamp column to the history table or add a running MD5 to make it harder to remove records without evidence.