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.

  • How to get difference between two rows for a column field?
  • Cannot access SQL Server database using jtds
  • Query to search an alphanumeric string in a non-alphanumeric column
  • Fuzzy grouping in SQL
  • SQL Count Grouping by a sequence of numbers
  • Check constraint UDF with multiple input parameters not working
  • 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.

  • Which DataType should be used for Editor & File and image browser
  • how to save html to a database field
  • “Unable to find the requested .Net Framework Data Provider. It may not be installed.”
  • “The transaction log for database is full due to 'LOG_BACKUP'” in a shared host
  • Entity Framework 6.1 : The given key was not present in the dictionary
  • EF5: Cannot attach the file ‘{0}' as database '{1}'
  • 3 Solutions collect form web for “Create, Approve, Edit, Approve pattern for site content”

    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.

    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.