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
Always start db design with checking how you will access the data, how many rows will it become, how many revisions there will be, and optimise for that. I would only make a second table revisions if there are a lot of revisions, but there are not a lot of queries on it. Ie your website will always show the latest view, and people actually have to click to a second page to see/edit revisions. Then you select on table Teams and have the latest info, and only if people want to see revisions you do a select on table Revisions. If you want to show both the latest version and revision info at the same time, you'll need to either join over both tables or put everythign in 1 table. Don't have a revisions table, but just a team table, and add a revision_id or another indicator (IsLatestRevision as boolean) to keep track of what is the latest and what are older revisions. Then you can just select everything out of 1 table. There are some other solutions possible but it depends on how you will query the data most.. Don't do your 3rd solution as it'll be very difficult to join. Sol 2 is not good as your want all your latest (correct) data to be in 1 table, not spread over 2.