Page 1 of 1
Database Design - Store "edited" version of record
Posted: Thu Aug 24, 2006 11:46 am
by Luke
I need to be able to store a record, and then when it is edited, it needs to store both the original version and the edited version so that when the admin looks at it within my gui, they will see
Code: Select all
Property:
name: The Property
address: 1234 Some Road
(Unapproved edit: 1243 Some Road 8/24/06)
type: Commercial
price: $100,000
(Unapproved edit: $110,000)
Until they approve it. In which case those "Unapproved edits" will disappear and the records will merge. I am having a hard time designing the database. Any suggestions/advice?
Posted: Thu Aug 24, 2006 11:53 am
by feyd
hmm... I think they could occupy the same table whereby each record would have a possible record pointer to it's "parent." When changes are made you compare against the original. Where they do not differ, NULL the field. Only storing the changed columns (and the now parent record identifier)
It could be a separate table as an alternative, but the layout would be the same for the edits table. Probably need a timestamp kept in there if you allow multiple edits before approval, although you could combine the edits into a single edit record.
This is a rough DIFF table.
Posted: Thu Aug 24, 2006 12:01 pm
by Luke
So within the same table (I like that idea) make a parent_id column, and only store edited values within the child? How would I compare whether there was a difference in mysql?
Posted: Thu Aug 24, 2006 12:16 pm
by feyd
Couple of options: do the compare in PHP or add conditionals and inject the originals as part of the equation.
Another idea: a single record approach, where you have a duplicate column (that can be NULL) of each of the normal columns. The benefit here is that when doing an edit update it can all be done in a single update query.
You'll have to be careful with race conditions for all the ideas I've thrown out here. Basically you'll need to check if a person's edit could wipe out another's edit of the same record when they were editted at the same time. How I would detect this is the timestamp idea I threw out there. In the edit form store the timestamp that was in the record when they started. If the timestamp has changed when they submit again, someone else has editted the record inbetween. They could be given a page that has three inputs for each field: one being their changes, another being the other person's edits and the third being the one that's actually submitted. This page should store the new timestamp incase yet another edit happend inbetween those.
Weee, fun stuff.

Posted: Thu Aug 24, 2006 12:20 pm
by Luke
Actually, the way this is set up, only one person should be able to edit any property at a time anyway because it is user-based. You can only edit YOUR properties. So that stuff shouldn't actually be a problem.