Hi,
I'm trying to figure out how I should save an edit a user makes to their profile in a way that doesn't go live or affect the existing live data until it's approved by an admin. Have a second table for edited profiles then copy over the data on approval? Keep it all in one table and have a _tmp copy of all the fields that are editable? Is there a best practice for this?
Thanks for your thoughts.
You can build a bit of workflow into your application. So to that effect, you'll have a Workflow table that will have defined various states (e.g. entered, proposed, approved, etc...).
Then you can also have a PendingChanges table that stores these proposed changes. When the the proposed change has been approved, you merge the change into the main user profile change.
Having a separate table sounds good to me. That way you can store only what's changed, not everything else.
If you have many cases like this (across many different tables) you could have a TempObject table where you serialize changes to XML or some other state until they are approved.
Or if it is just the user profile table, you could have a unique key on UserID + Approved(boolean). When the user edits their data, it goes into the table as UserID, Approved = false, then to approve it, you just delete the approved one and update the not approved one to approved (in a transaction of course).
After all, you already have the structure to hold all that data - why not reuse it?
For simplicity, I will often use some 'status' column in the database to determine if a particular row is publicly viewable. In your SQL, you would then add
WHERE status = 'published'
That works well for simple sites.
For busier sites, I suspect there is some performance gain by not having that WHERE clause. Having pending edits in a separate table would be a good option, and then you INSERT INTO ... SELECT FROM to move it to the live table.
A second table in the same format as the first doesn't easily allow for multiple changes to be queued.
I would recommend designing a specific tructure to record each change request as a change request. Fields for who it's changing, what is changing, what to, who made the request, when, etc.
Then have code to apply the change if/when validated.
This can also then act as an easy to track audit trail.
I would not make the changes in the same table, it tighly binds the implementations together and makes later maintenance a headache. The independance reduces how closely coupled everything is, for more flexibility in the future.
This seems simplest: You could add a VERSION and a STATUS field to the USERS table. Then, use the STATUS field to show the highest VERSIONed row, as appropriate. Obviously this gives you versioning of the records as well.
As long as VERSION and STATUS are indexed, they won't really slow down any display operations. Add rows will be slighly slower, as the indexes must be maintained.