Revision history

[Philip]

I'm interested to see how others handle revision history within a database (specifically MySQL.)

 

Let's say I have this (rather simple) table that I want to store a revision history on:

mysql> describe table;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(125)         | YES  |     | NULL    |                |
| email | varchar(360)         | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+

 

I've seen the following ways to do store revisions, but which do you think is the best?

 

Store the revision data in one table, and the current data in another

 

mysql> describe live_table;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(125)         | YES  |     | NULL    |                |
| email | varchar(360)         | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+

Then the revision data (where item_id links to id in the table above):

mysql> describe revision_table;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| revision_id     | int(10) unsigned | NO   | PRI | NULL    |                |
| item_id         | smallint(6)      | NO   | PRI | NULL    |                |
| name            | varchar(125)     | YES  |     | NULL    |                |
| email           | varchar(360)     | YES  |     | NULL    |                |
| changed_user_id | smallint(6)      | YES  |     | NULL    |                |
| changed_date    | timestamp        | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

This method requires a few more queries I would think: copying the current row into the revision table (including upping the revision_id from the previous revision in the revision_table) and then inserting new data.

 

Store all of the data in one table

mysql> describe table;
+-----------------+----------------------+------+-----+---------+----------------+
| Field           | Type                 | Null | Key | Default | Extra          |
+-----------------+----------------------+------+-----+---------+----------------+
| revision_id     | int(10) unsigned     | NO   | PRI | NULL    |                |
| item_id         | smallint(6) unsigned | NO   | PRI | NULL    | auto_increment |
| name            | varchar(125)         | YES  |     | NULL    |                |
| email           | varchar(360)         | YES  |     | NULL    |                |
| changed_user_id | smallint(6)          | YES  |     | NULL    |                |
| changed_date    | timestamp            | YES  |     | NULL    |                |
+-----------------+----------------------+------+-----+---------+----------------+

And then just select the item_id/highest revision_id to get the current row.

 

Same approach as the first, but store the data in one field (using serialize/json_encode)

mysql> describe live_table;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(125)         | YES  |     | NULL    |                |
| email | varchar(360)         | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+

Then the revision data (where item_id links to id in the table above):

mysql> describe revision_table;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| revision_id     | int(10) unsigned | NO   | PRI | NULL    |                |
| item_id         | smallint(6)      | NO   | PRI | NULL    |                |
| data            | text             | YES  |     | NULL    |                |
| changed_user_id | smallint(6)      | YES  |     | NULL    |                |
| changed_date    | timestamp        | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

 

 

Or am I way off-track?

[scootstah]

I like the second method, storing it in one table and selecting the highest revision.