Create, Approve, Edit, Approve pattern for site content

I'm working on a modification to a site to allowing users to enter content. However, once they've created that content it needs to be approved before it can be published. This is fairly easy to achieve by having a "moderate" bit set in the record for that content and only one table is needed.

Now I also want to allow users to edit the content much like here on StackOverflow. However, each edit needs to be approved as well as being stored so that a history of edits can be viewed.

It is acceptable to prevent editing to the "live" record while an edit is pending approval so conflict of pending edits is not an issue.

My intention is to keep the collection of previous versions of each item in a separate table and have the pending edit sit in this table until approved/rejected and then if approved swapped with the record in the main table.

Can anyone see any flaws in this pattern? Can you think of a better way to do this?

Although I don't think that it's relevant I'm using C# with ASP.NET MVC and SQL Server as the data store.

From stackoverflow Guy

• About the only things that springs to my mind is timing.

  If the edit is time critical, like a promotion or offer, then the approval process will need to be pretty snappy also.

  You may want to consider building in an email function, or priority flag, so that edits can be approved in a timely manner.

  One other consideration, and I don't know if time allows for this, is to maybe incorporate Windows Workflow Foundation. You could attach the edit record id to a workflow item that then handles the timeliness of the approval.

  It also means that if an approver is away, that someone else could pick it up as it's a workflow item that has a queue and if you are a member of the queue you'll see it.

  I know that's a heck of a lot more work but I think a better solution than just relying on someone to come along and approve the change.

  From griegs

• What about something like this:

  • One table for the items and one table for all revisions. The revisions have an approved flag.
  • View
    • Users get the latest approved revision.
    • Moderators get the latest revision and the option to approve it.
  • Edit
    • Disabled for users when there is a newer revision than the latest approved revision.
    • Always enabled for moderators.
  • History
    • Shows only approved revisions to users.
    • Shows all revisions to moderators.

  ---

  It is acceptable to prevent editing to the "live" record while an edit is pending approval so conflict of pending edits is not an issue.

  I know a website that does this. I personally find it very annoying. I wish they'd do it like this:

  • One table for the items and one table for all revisions. The revisions have an approved flag.
  • View
    • Guests get the latest approved revision.
    • Registered users can choose between the latest and the latest approved revision.
    • Moderators get the latest revision and the option to approve it.
  • Edit
    • Disabled for guests.
    • Users can revise the latest revision (approved or not).
    • Always enabled for moderators.
  • History
    • Shows only approved revisions to guests.
    • Shows all revisions to moderators and registered users.
  cdonner : Stackoverflow takes a "last update wins" approach. If 2 people edit the same question, the changes of the 1st person who saves will be lost. This is what you are recommending, I think, but it may not be appropriate for content that is less short-lived and "slower" than this here. Locking content is a more sensible approach, but in a web setting you have to challenge that you don't know when a lock was abandoned. Joomla has this problem. When I close my browser, the article remains locked until an admin releases it.

  dtb : There's a difference between locking an item while editing it and locking it after it has been changed until it gets approved by a moderator. I'm in favour of the "last update wins" approach for the former, but for the latter I'd prefer if the item could be edited before the revision is approved.
  From dtb

• I have worked with a few of the major commercial CMS, and I don't remember any of their workflows creating a physical copy of the content when it is approved. I can see that it is a tempting idea, though. You want to prevent unapproved content from leaking out into the open, etc, but I don't think there is a good technical reason for doing that (unless you expect so much content that partitioning it into published and unpublished sets has a performance benefit).

  I don't think that there is anything particularly wrong with your approach. But you are duplicating data for no good reason.

  Guy : The pattern does not include the duplication of content. Once approved, the new content would be moved to the active table and the previous record in the active table would be moved to the edits table. They would be swapped.
  From cdonner