I simply beginning a task, I must possess a small content manager with version control. However I'm not sure what's the easiest method to model the database.

I've content table which consists of the next fields: id primary key serial, content_id int (area to recognize diferent contents), title varchar, content longtext, version int default '1', create_date date,

I've come across some Content management systems separes the revisions in another table compared to actual revision, What's the easiest way? Can there be any enhanced way?

Thanks!

I designed something similar to this and here's the gist from it

  1. I create mirror table for each table that I wish to have row level version control. Let us if you have CUSTOMER table. Your mirror version control table is going to be VER_CUSTOMER
  2. Every table that I wish to have row level version control includes a column known as RECORD_ID (GUID)
  3. Whenever a record placed to that particular table, I generate new GUID and populate that area. New record also placed into VER_CUSTOMER table with RECORD_ID as put into table's natural PK.
  4. When record is up-to-date, I generate new GUID again. Populate RECORD_ID with this particular new GUID. Up-to-date record also would go to VER_CUSTOMER table.
  5. When record is erased, I mark record on CUSTOMER table as Erased (not physically remove the record). I've IS_Erased column on every table. I set that column to TRUE when record is tried to be erased. Again copy from the erased record also adopts VER_CUSTOMER table.

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

Hope this can help...

This already is available, with no database:

  • gitit (designed in Haskell, uses git or darcs like a after sales)
  • ikiwiki (designed in Perl, may use various version control systems like a after sales)

They are both free, and have a wordpress plugin architecture, so could be customised for the specific needs. (However, I have only used gitit.)

I'd however observe that git isn't perfect at versioning large binary files, and darcs is terrible in internet marketing. Something to be careful for.