Is there a best practice for maintaining history in a database?

Question

I don't do database work that often so this is totally unfamiliar territory for me.

I have a table with a bunch of records that users can update. However, I now want to keep a history of their changes just in case they want to rollback. Rollback in this case is not the db rollback but more like revert changes two weeks later when they realized that they made a mistake. The distinction being that I can't have a transaction do the job.

Is the current practice to use a separate table, or just a flag in the current table?

It's a small database, 5 tables each with < 6 columns, < 1000 rows total.

Answer 1

FROM THIS QUESTION

How to keep history of record updates in MySQL?

One simple way to keep version history is to create basically an identical table (eg. with _version suffix). Both of the tables would have a version field, which for the main table you increment for every update you do. The version table would have a composite primary key on (id, version).

Whenever you do an update on the actual table, you also INSERT a new row in the version table with duplicate data. Whenever you want to find the version history, all you need to do is something such as SELECT * FROM content_version WHERE id = CONTENT_ID ORDER BY version.

If you use something like Doctrine ORM, it has a behavior that does this for you automatically via event listeners. You can check it out here: http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors#core-behaviors:versionable

OR

The easiest solution (depending on your specific needs) would probably be to add an on update/insert/delete trigger to your table, so you can perform extra logging when data is inserted/updated/deleted. That way even manual interventions on the db will be covered...

Check http://dev.mysql.com/doc/refman/5.1/en/triggers.html for more information.