Versioned Content

[thehippy]

I'm still at the planning stages of a website, I'll be using PHP5, Postgres 8.2 and a framework, maybe the Zend Framework or CodeIgniter.

 

I'm having a hard time trying to come up with a process for versioned content. What I mean by versioned content, I'll give you an example of how I want to use it. I want to be able to moderate content, on a page for a single book listing, a user can update the content of that page, perhaps a misspelling or additional information, but it must be approved.  There may possibly be several new versions of the content waiting for approval as well.  Being able to rollback data to a previous state may be useful to, but I haven't really seen the need for that quite yet.

 

What I've come up with is just having an entry in my db book table with perhaps with a version field and another table to track which version of book data I should be using.  This just doesn't seem right though, I'll be duplicating a lot of data this way.  Also my data is managed over more then a few tables, if it were just one table's worth of data maybe my idea would work, but when its three to six I can only see it as impractical.

 

So could someone offer me some practical advice? What is the more practised approach?

[Buyocat]

Off the top of my head one solution would be to leverage Subversion and its version control directly.

[thehippy]

I thought I would look into wiki's for perhaps some insight into versioned content.  I looked at mediawiki and tikiwiki.  They both save the entire entry at each new revision; any kind of diff'ing is done at runtime and there's lots and lots of caching going on.  With wiki's each pages' content is isolated to a single table (for data) and just needs to be parsed then rendered, where what I have is lots of relationships with data across many tables. 

 

I came up with another idea too, create a table to act as a queue for SQL inserts/updates.  Save the raw SQL that way and extract the data from the statements when a moderator has a chance to approve or revoke the update.  Only adding the data that was approved and with the new data a revision counter.  This will mean lots of revision fields in my tables though, something I can deal with.

 

SQL statements in SQL data might not work so well...

[Buyocat]

Out of curiousity why is it a problem to have revisions stored as rows in one or more tables?  You could have something like:

 

table Article

id

revision

date_created

date_modified

 

table ArticleBody

article_id

revision

body

// note that the key for the articlebody table would be the article id + revision, so no row could have the same revision and article id.

 

You could also make a table for article titles.

 

Reading would be easy as you would just read from the article table fetch the id and revision and then have the key for a single row in the remaining tables.  Writing would be more challenging because updates would be barred in the dependent tables.  Instead you would need to make a scheme to increment the revision value by one for a given article.  Of course this isn't really that hard to do, especially considering that you should always have that information on hand at runtime.

 

Let me hear why you think this wouldn't work, Buyo.

 

 

[thehippy]

table books

-----------

id

eng_title

eng_synopsis

pub_date_begin

pub_date_end

publisher_id FK

|

|

|

|

|

|

|

|

table titles

------------

id

lang_id FK

title

|

|

|

|

|

|

|

|

table staff

-----------

id

family_name

given_name

date_birth

date_death

|

|

|

|

|

|

|

|

table publishers

----------------

id

name

market_symbol

country FK

|

|

|

|

|

|

|

|

table staff

-----

id

staff_id

role FK

book_id FK

*omitted some tables, obvious what they contain.

 

This is a simplified version of how I've broken down my data as of now.  All of these tables are used in a simple book listing.  If a user wanted to submit the correct spelling of the books' cover artist's last name and the books' title in German, how do I track that as a single update so a moderator can approve or revoke the update? Should I even hope that what I want is possible? I think what I've thought up is too complicated for my dataset.  This is giving me a headache. I'll just have to have a suggestions box link per book and have a moderator manually add the data themselves; just so I can move on from this for now

 

Buyocat, if I understand how you advise to do it, I'll need a table for every field that can be updated by users. Your idea works for your example.