How to implement moderation?

What approach would you take to make a solid moderation system?

I have built a couple of franchise websites and the approach that I took was to have a second duplicate table which I post new moderations to. The only thing is that all the columns can be null and also it has a foreign key pointing back at the real post.

The idea is that each franchise can edit their profile for their individual company and then head offices approves it.

The system I have built is designed to handle a low volume of updates but it has the following problems:

• If somebody posts a change and then wants to post again before the moderation has been approved then they still see the original content which can lead to confusion.
• The record is always compared against the live record rather than the state it was in when the moderation was submitted
• For text descriptions it is hard to see where the actual changes are as it could just be one word in a block of text.
• Large amount of code in the dal which is tied directly to moving one column in the live data to a column in the moderated data. The approval page is the same - lots of checks for nulls to see if that col should be being moderated. I dont like it being tied to the model so closely.

So... its a Saturday evening here and its been a nice sunny day. I just had a few minutes to reflect on the project and wondered if there was an obvious approach I had missed.

How is it implemented on this site?

Come on....  have a go!

This is a theory only question - there are no wrong answers :)

A suggestion: instead of having a separate table, you can just have a column like UpdatedContent. This column would initially be empty, but once user modifies its data, it would store the updated content. You can also add a Status Column, something like, Published, Declined, and Updated. When the user updates an already published value, the status would be set to Updated and you can show the old content on the front end, and content from UpdatedContent column in the admin/manage section. Once the content is approved, you can copy both columsn to the same value and change the status.

Let me know if you see any issues with this.

Vivek

Hey,

Thanks for the feedback!

I think that is a good idea and it definitely solves some of the problems.

I was thinking about this again the other day and I thought that if I had to solve it again I would add a new nullable column called ParentId which pointed at the real record and then stored updated information in it as a kind of shadow version.

You could chain up multiple amendments or code it so that any updates to items in the queue simply discards the existing updates.

The problem that neither of our solutions solve is that sooner or later you are going to have to code a routine that copies each of the columns from moderated content to live content. I didnt like this limitation being in there. You could probably do this with reflection over a Linq2Sql DataContext (which is what I work in) but I haven't got a clue how you would do it.

There is also the other problem that I raised with comparing two blocks of descriptive text against each other. I wonder if there is some kind of text comparison algorithm out there that would highlight the changes between two blocks of text?