Approving changes to live data using Entity Framework

I have a requirement that only super-admins can modify "live" data.

Currently, if an ordinary admin wants to make a change to live data:-

1. A super-admin unpublishes the data
2. The ordinary admin makes their changes
3. A super-admin approves the changes and publishes the data

What are my options if I want to try to eliminate step 1 and have the old data remain live until the super-user approves the changes?

I'm using ASP.NET MVC and EF4 and so I'm particularly interested in solutions around EF that can be made transparent to my controller, ho开发者_StackOverflow社区wever I'm also interested to hear about solutions in the database layer (or for entirely different contexts)

First of all, since you're talking about role-based activities taking place, I find it hard to come up with an appropriate solution that doesn't involve the application/business tier. I believe your data access layer should be oblivious to the fact that a super-admin or an ordinary admin is calling its methods.

In any case, one way to handle this would be to create a PendingChanges table and save ordinary admins' changes to it with a flag indicating that it's pending approval. Then once the super-admin approves it, you copy the record from PendingChanges into the live table.

I will try to give a simple Object graph example.

Consider a blog with following structure.

Posts
  PostID
  Title
  Description

PostUpdates
  PostUpdateID
  PostID
  Title
  Description
  DateUpdated
  UpdatedBy
  ApprovedBy
  PostStatus(Approved/Rejected)

Here my logic will be to display Post and it's Last Updated and Approved PostUpdate. Now in this case, nothing is actually modified ever. Always a new set of changes are appended, you can decide to delete old versions.

For Save, you add a new PostUpdate entry in PostUpdates.

For Super Admin, you can display all updates and let him approve the correct one. When admin decides to approve the update, Post will be modified with contents of last approved PostUpdate.

Another Alternative (More Generic)

You can create a table as follow,

RowUpdates
   RowUpdateID
   TableName
   TableKey
   NewValues (kind of XML store to save which fields were modified)
   UpdatedBy
   ApprovedBy
   DateUpdated
   UpdateStatus (Approved/Rejected)

But in this case you will have to use reflection to avoid storing values to DB and instead create new RowUpdate entry and only if the admin approves it, actual values should be posted to db after doing reflection mapping.