Allowing Collaborative Editing
Posted by: M K
Date: June 27, 2007 03:36PM

Hello All -

I'm developing a site that is going to allow community/member editing of certain items, but I'm not sure how to structure the database efficiently for tracking changes...

The current structure is similar to this:
table: teams
teamid, addedby, updatedby, teamname, teamaddress, teamcity, teamstate, teamzip, teamphone, created, modified


Users will be able to update each of these fields (except id of course) to correct any mistakes or updated info (such as a new phone number).

So far, I've come up with 2 possible solutions, but I'm not in love with either of them and I don't think either is proper/efficient.

1) Create a revisions table. Before updating the teams table with new information, copy the data to the revisions table, allowing users to see past iterations and revert back easily, if necessary.
table: revisions
revisionid, teamid, updatedby, teamname, teamaddress, teamcity, teamstate, teamzip, teamphone, created, modified

2) Store the original data in the teams table. Create a revisions table that will hold each revision. When pulling the data to display, sort by the latest modified for that teamid in the revisions table

3) Store the original data in the teams table. Create a revisions table that stores just the piece(s) of data that were updated. When selecting the data to display, choose the latest modified for that piece of information and teamid.
table: revisions
revisionid, teamid, fieldname, fieldval, modified
example: 1,1,teamname, Badgers, 2007-06-27 15:15:15


Any thoughts, advice? Thanks

Options: ReplyQuote


Subject
Written By
Posted
Allowing Collaborative Editing
M K
June 27, 2007 03:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.