Hi, I’m going to be developing a site where users will have the opportunity to edit previously submitted form data. What I have been asked to do is to track all edits via some form of versioning system in the database. I’m still not entirely sure of the structure of the data i’ll be working with yet, but I’m trying to think of the best approach for this type of versioning system. If i assume that the majority of the tables in the database will have editable data, then i need to come up with a sensible and scalable approach to this.
At the moment I’m considering a simple approach where instead of updating a row with edited data, a new row is created in the same table. When retrieving data for display, the query would retrieve the data based on the latest timestamped row (or some form of flag to indicate version order). One problem I could have here is when changes to one table should cascade down to related tables. Enforcing referential integrity with InnoDB would probably not work with this approach as new rows/ids would be getting generated. This might require another approach where I set up triggers which are activated on an UPDATE statement and which handle all the necessary cross table data changes.
If anyone has any suggestions on a good general approach for handling this type of thing, i’d greatly appreciate some advice!