Revision control for multiple pieces of related data

Question

I'm trying to figure out how to best keep revision/history information on revisions to multiple rows of data, in case for some reason we need to revert to that data.

This is the general sort of layout:

item
---------------
id
title
etc...

region
---------------
id
title
etc...

release_type
-----------------
id
title
etc...

items_released_dates_data
---------------------
item_id
region_id
release_type_id (these three form the primary key)
date

So you can have one release date per item + region_id + release_type and we basically only track the date (For the purposes of this question the 'date' could be a number, a string, or whatever. I'm certain to run into this issue again)

Changes are submitted in bulk, when new data is added everything in items_released_dates_data where item_id=your_id is first deleted then an insert statement adds the new values (perhaps this isn't the best way to do this?)

My thought was to create a table like:

items_release_dates_data_history
-------------------------------------
item_id
timestamp
description
raw_data

Making description a short summary of what was updated, and including the data in some format like json or xml or something that could be quickly decoded on the client side to give the user a review of the changes and a choice to revise to a given version. Then every entry to items_released_dates_data also requires an entry to items_released_dates_data_history (doesn't sound like a question does it? :| )

I've read something about mysql triggers that would be helpful here, but quite frankly I don't know a thing about them so I'm working with what I understand.

My question is, am I following the right path to version this stuff, and is there any advice/best practices anyone can give me on how to improve this method?

Answer 1

I second Alex Miller's comment. Everything you write make sense so far.

I'd strongly recommend looking into triggers though, despite your reservations. They're fairly easy to grasp, and make for a very powerful tool in such scenarios. Using triggers you can store a copy of the row into a separate table each time a record is updated (or deleted). If you want to go all fancy you can, within the trigger, compare the incoming data to the existing data, and write only what has changed.

Also consider the Archive storage engine instead of MyISAM or InnoDB for these kinds of tables - they're made for this kind of job.

Also, the search phrase you're probably looking for is "audit trail".