Keeping page changes history. A bit like SO does for revisions

Question

I have a CMS system that stores data across tables like this:

Entries Table
+----+-------+------+--------+--------+
| id | title | text | index1 | index2 |
+----+-------+------+--------+--------+

Entries META Table
+----+----------+-------+-------+
| id | entry_id | value | param |
+----+----------+-------+-------+

Files Table
+----+----------+----------+
| id | entry_id | filename |
+----+----------+----------+

Entries-to-Tags Table
+----+----------+--------+
| id | entry_id | tag_id |
+----+----------+--------+

Tags Table
+----+-----+
| id | tag |
+----+-----+

I am in trying to implement a revision system, a bit like SO has. If I was just doing it for the Entries Table I was planning to just keep a copy of all changes to that table in a separate table. As I have to do it for at least 4 tables (the TAGS table doesn't need to have revisions) this doesn't seem at all like an elegant solution.

How would you guys do it?

Please notice that the Meta Tables are modeled in EAV (entity-attribute-value).

Thank you in advance.

Answer 1

Hi am currently working on solution to similar problem, I am solving it by splitting my tables into two, a control table and a data table. The control table will contain a primary key and reference into the data table, the data table will contain auto increment revision key and the control table's primary key as a foreign key.

taking your entries table as an example

Entries Table
+----+-------+------+--------+--------+
| id | title | text | index1 | index2 |
+----+-------+------+--------+--------+

becomes

entries             entries_data
+----+----------+   +----------+----+--------+------+--------+--------+
| id | revision |   | revision | id |  title | text | index1 | index2 |
+----+----------+   +----------+----+--------+------+--------+--------+

to query

select * from entries join entries_data on entries.revision = entries_data.revision;

instead of updating the entries_data table you use an insert statement and then update the entries table's revision with the new revision of the entries table.

The advantage of this system is that you can move to different revisions simply by changing the revision property within the entries table. The disadvantage is you need to update your queries. I am currently integrating this into an ORM layer so the developers don't have worry about writing SQL anyway. Another idea I am toying with is for there to be a centralised revision table which all the data tables use. This would allow you to describe the state of the database with a single revision number, similar to how subversion revision numbers work.