Data Versioning Advice

Question

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. This could take the form of just creating mirrors of tables with extra columns to record date/version number.

If anyone has any suggestions on a good general approach for handling this type of thing, i'd greatly appreciate some advice!

Answer 1

It's a good and deep question, which undoubtfully cannot be seriously answered without understanding the types of workloads you will be dealing, the actual queries to be performed (you should profile them!), the variety of information to be versioned, and so forth.

But as for a general approach, I'd start with a simple solution which you can experiment with and benchmark. From that specific case study, develop a more robust case that you elaborate using lessons-learnt from your initial attempt.

Here is one good example right here: Database - Data Versioning

Other examples exist, and reading your question I believe you need not just the audit trail, but the actual information as well (to be able to return in time to a specific version).

In response to your specific question, whether to use triggers, I would approach it differently: have your business logic in stored procedures for Transactional-update/delete etc', which you can later update and make more complex. Why? because it's in "one place" and nothing can get around it, and because triggers are more associated with tables and atomic actions (update, delete) rather than transactions!!

For performance, I'd simply mark the active record as part of the update stored procedure (or high level language procedure for that matter), instead of querying for the most recent version.

Answer 2

A trigger is a good solution. I myself prefer this type of logic to be included in my application, so I keep it in the business logic layer of my application.