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

Have a look at this question: http://stackoverflow.com/questions/323065/how-to-version-control-a-record-in-a-database

Why not have a separate history_table for each table (as per the accepted answer on the linked question)? That simply has a compound primary key of the original tables' PK and the revision number. You will still need to store the data somewhere after all.

Answer 2

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.

Answer 3

For one of our projects we went the following way:

Entries Table
+----+-----------+---------+
| id | date_from | date_to |
+----+--------_--+---------+

EntryProperties Table
+----------+-----------+-------+------+--------+--------+
| entry_id | date_from | title | text | index1 | index2 |
+----------+-----------+-------+------+--------+--------+

Pretty much complicated, still allows to keep track of full object's lifecycle. So for querying active entities we were going for:

SELECT 
entry_id, title, text, index1, index2
FROM
Entities INNER JOIN EntityProperties
ON Entities.id = EntityProperties.entity_id
AND Entities.date_to IS NULL
AND EntityProperties.date_to IS NULL

The only concern was for a situation with entity being removed (so we put a date_to there) and then restored by admin. Using given scheme there's no way to track such kind of tricks.

Overall downside of any attempt like that is obvious - you've to write tons of TSQL where non-versioned DBs will go for something like select A join B.