views:

166

answers:

2

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 effected 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 sepperate 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 corasponding file. So object will store the current hash (sha256) of the file also. This hash is used for deduplication purposes.

A: 

How about

objects object_chunks revision object_attributes

Where revision is an increasing number, you could simply select the objects with max(revision) grouping by object, object_chunks in the future.

MindStalker
Basically my #2, would involve making a new entry for each revision. Although unlike yours, I would just be inserting the "new" data, and updating the main row(s) with the current as it would be much more efficient than max().
William
+1  A: 

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.

Ken Fox
+1 for the point of storing it all in one table. Although I don't think I'll change the PK as it will be linked all over the system. I like the idea of keeping the revision number in a separate field and then just creating a new entry anytime the main data changes. Sure, this would require an update + insert instead of an insert, but I think it will make it easier to work with. Although I'd like to hear the reasoning behind your method.
William
The rev column must be part of the PK because object attributes are associated with a specific rev. You build the objects with an explicit rev instead of trying to juggle revisions behind the scenes. Databases are not file systems--complex schemas and integrity constraints make it very difficult to implement external revisioning logic compared to just adding a "rev" column to the PK. The exception is if your PK is synthesized, e.g. an auto-increment object id, then you shouldn't add rev to the PK and can instead map object (name, revision) to object (id) in a separate table.
Ken Fox
Ken Fox, yeah I'm using a AI column, thats what threw me off. I have a new possible solution I'll post soon that goes over your points along with mine. Would like to get your opinion.
William