Content Version Control

I just starting a project, I would like to have a small content manager with version control. However I don't know what is the best way to model the database.

I have content table which contains the following fields: id primary key serial, content_id int (field to identify diferent contents), title varchar, content longtext, version int default '1', create_date date,

I have seen some CMS separes the revisions in another table than the actual revis开发者_运维知识库ion, What's the best way? Is there any optimized way?

Thanks!

I designed something like this and here's the gist of it;

1. I create mirror table for every table that I want to have row level version control. Let's say you have CUSTOMER table. Your mirror version control table will be VER_CUSTOMER
2. Every table that I want to have row level version control has a column called RECORD_ID (GUID)
3. When a record inserted to that table, I generate new GUID and populate that field. New record also inserted into VER_CUSTOMER table with RECORD_ID as added to table's natural PK.
4. When record is updated, I generate new GUID again. Populate RECORD_ID with this new GUID. Updated record also goes to VER_CUSTOMER table.
5. When record is deleted, I mark record on CUSTOMER table as DELETED (not physically delete the record). I have IS_DELETED column on every table. I set that column to TRUE when record is attempted to be deleted. Again copy of the deleted record also goes into VER_CUSTOMER table.

So every transaction that you have on that table, you have a corresponding record in VER_CUSTOMER table with RECORD_ID and table's natural PK as PK. For example if CUSTOMER table's PK is CUST_ID. PK of VER_CUSTOMER will be composite CUST_ID and RECORD_ID.

Hope this helps...

This already exists, without a database:

• gitit (written in Haskell, uses git or darcs as a backend)
• ikiwiki (written in Perl, can use various version control systems as a backend)

They're both open source, and both have a plugin architecture, so can be customised for your specific needs. (However, I've only used gitit.)

I would however note that git is not perfect at versioning large binary files, and darcs is terrible at it. Something to watch out for.