on Mar 28th, 2006Keeping track of user-made changes
In my web application I have been building for over a year, registered users can edit content. This is structured content that is stored in different fields and database tables. Every change that a user makes needs to be approved by a moderator/admin user. There are a couple of ways you can do this, and I’m honestly not sure which way is the best.
- Seperate Table
- Create a seperate table that looks just like the parent table with the addition of a pending_id and pending_user_id column. The problem with this is code duplication. You will have to make sure that any changes to the original structure are made to the “pending approval” table structure.
- Serialize the “diff” and store that in a new table
- The changes made by the user are serialized to a string and stored in a new “pending_changes” table, along with the user who made the changes. This is very similar to the above method, except you don’t have to worry about keeping both tables synchronized. The problems with this method are the difficulty in upgrading. If you add/delete/change columns from the original table, the serialized “pending change” objects might deserialize correctly.
- Use the same table to store the “pending approval” items.
- 2 columns would need to be added to the original table. pending_id(id of the parent item) and pending_user_id(id of the user who made the changes). The problem with this solution is it’s very bug prone. You have to remember to add “pending_id IS NULL” to every single sql query. The risk can be reduced by unit and functional tests, but it’s still not ideal. In Rails this could easily be done with Single Table Inheritance, but you still have the problem with adding the pending_id filter to every sql query.
I’ve tried the 3rd method before in a rails application and it was painful. I’m not too sure if the other methods would be any better. anyone have any other ideas?
I would serialize the diff. I would imagine if the app gets big and entries grow, the most scalabe option would be to serialize diff. Once implemented it would serve small and large communities equally with the added benefits of data stores low. i wouldnt worry so much for the table definition… just roll with the punches if a change is necessary. i wouldn’t let that hold u up.
[…] In my Keeping track of user-made changes post I descriped the various options for implementing change-tracking in my application. I ended up doing something completely different. Ruby on Rails has a cool feature called Observers which basically act like database triggers. After certain events(save, update, create, etc) happen your observer code will automatically get executed. […]