MySQL Row-level Revision Control

Question

I’m creating revision control for data in our database. It will have the ability to store revisions, rollback, and rollback rollbacks. The database tables I’m using that needs to be revisioned is below:

objects
object_chunks
object_attributes

Objects is the main object, chunks are grouped up sections of the object, and attributes are attributes of data inside a chunk. Attributes stores the object ID along with the chunk ID that way It’s easy to select all the attributes for an object without having to do another JOIN to the chunks table.

The only thing that will ever really change is attributes, but when an attribute changes, the affected chunk(s) will be updated, and whenever a chunk is updated, the object gets updated also. Now I’ve thought of two different ways of solving this problem.

1. Create three new tables with a suffix of _rev, these tables would simply store older versions of the objects. The real objects would also store a rev number. So lets say I changed three different attributes, these attributes spanned across three chunks, so three new rows in chunks, three in attributes, and one in object for revisions. Since this is the first change, the rev ID would be 1, in the real tables, their rev would be 2.
2. I’d simply do the above, but instead of having a separate table, I’d simply store it in the same table.

One thing to note, there will ALWAYS be revisions, the amount of chunks can vary from 1 to 100+. Although the average is around 1-15. The attributes can vary from 0 to 100+. The average is probably around 30. EVERY attribute WILL change. These objects get put through a “phase” where all attributes must be filled out by users. Once they’re filled, the object is archived and never modified again. All objects have a corresponding file. So object will store the current hash (sha256) of the file also. This hash is used for deduplication purposes.

Answer 1

Adding a revision id to the primary key of the objects table is definitely the way to go. You can have multiple active revisions and not have to move data between tables. Going with multiple tables, you will find it difficult to write rollback algorithms moving data around while maintaining integrity constraints--especially difficult if the system is under active development.

If revisions are created in human time, a simple time stamp may work as a revision id. Otherwise just use an integer as the revision number--I've implemented CVS style dotted revision numbers and wished I hadn't. You can track derivation history in a separate table if people ask for the feature later.