best practice for create database for Versioning System like CMS

Question

Hi,
I am working with system like CMS that need to versioning documents.
what is best practice and methodology for create database for it with any how its table?

Answer 1

I would look first at how MediaWiki did it in their open source project that runs Wikipedia. They're all about versioning.

Answer 2

When I did this once I found it worked well to have a table structure like:

tbl.posts
  postid(PK) | title | category
tbl.revisions
  revisionid(PK) | postid(FK) | authorid(FK) | content | date

Fields that could be changed should go into the revisions table (e.g. content), things that won't go in the posts table (postid, category etc.).

From what I heard on the last podcast StackOverflow does something similar.

Answer 3

I prefer to store the new (the new revision) the same way as the posts when they are added the first time, but I have a column called "childOf" which stores the ID of parent post, the the query looks something like:

SELECT * FROM posts WHERE condition=value ORDER BY childOf DESC;

Answer 4

In my experience is better to have two different tables one with the data that doesn't need to be versioned, and another one with the data which needs to be versioned (as Spikolynn said in his/her previous answer).

The issue I've found when using only one table is that if other table has a foreign key pointing to that versioned table, as the primary key changes everytime the record is updated the reference is lost. This leads to very complex and inefficient queries in order to recover de data.

Answer 5

Chukc, what you could do to fix that problem is by adding an extra field and use that as the foreignKey

Let's say you have posts:

id, revision, name, content 

Revision would be:

id-publish or id-draft or id-revision-N

you then link your other table to revision id-publish instead of id. Just remember to index it :)