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.
- 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.
- 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.