Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

So let's say I have a DB table with 8 columns, one is a unique auto-incrementing used as ID. So I have a page that pulls in the info for each row based on query string ID. I want to give my users the ability to propose changes. Kinda like a wiki setup. So I was thinking I should just have another duplicate table or maybe database altogether (without the auto-incrementing column and maybe with a date edited column) that keeps all proposed changes in queue and then when I approve them, the script can move the row from the proposed DB to the real DB.

Does this sound good or is there a better process for this?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You have the right idea, but I would make the suggested edit table a bit differently. Your suggested edit table should include an auto increment key because there is no good reason not have one one as at least part of a key for any table. It should also have the key to the other table as a FK and a column for each of the columns users would be allowed to suggest edits to. Finally, there should be some audit columns, created date, modified date, created by, modified by, and a suggestion status column (accepted,rejected,pending). So your suggested edit table would look like:

PK | FK | other 7 columns | createdBy | CreatedDate | modifiedBy | modifieddate | Status

This will allow you to keep a detailed record of who suggested what and when and what gets approved/rejected and when. This also makes it trivial to create a script to update the table used for display with the edited columns when they become approved.

share|improve this answer
    
Cool that makes sense. Do you see those rows in the Edit Table staying there forever, with just status changing from pending to approved, etc... Or do you see the row being deleted after it's moved to the prod table? –  zen Jul 9 '12 at 19:43
1  
@zen the rows should stay there forever, though it may be necessary to archive and truncate the table occasionally for disk space issues. Having a historical record is important and it doesn't take much to retain the information indefinitely, it also will help you identify top contributors and spammers. –  Ryathal Jul 9 '12 at 19:59
    
Good point. Thanks! :0 –  zen Jul 9 '12 at 20:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.