I am database designer and at my current project I'm implementing versioning capabilities required to concurrently edit rows of data in RDBMS. The project requirements says, that data editing sessions can go on for several hours or days until performing commit. Also, conflicts are arising during simultaneous modifying of the same data by different users should be handled with possibility of manual and semi-automatic resolution. In other words, desired editing workflow is similar to one used in document-oriented version control systems, such as SVN or Git. Therefore, traditional OLTP approaches and conflict resolution strategies (MVCC, optimistic/pessimistic locks) doesn't satisfy my constraints. I have done some observation of existing tools, that offer possibilities for branched version history and multiversion workflow:

SQL:2011 doesn't solve my problem, as it offers support for "linear" history of edits, not branched I'm looking for. Solutions from ESRI and Oracle are good candidates, but I'm disappointed that both have vendor-specific interfaces for manipulating versions. It seems that at this moment nobody can offer industry standard solution for branched versioning of relational data (as SQL:2011 does for temporal tables and linear version history). As a newcoming database researcher, i want to understand:

  • is relational database community interested in developing standard models of branched data versioning and will any contribution or research in this area be valuable? (for example, standartization as it was done for temporal features in SQL2011 in the form of language improvements)
  • do developers and database designers lack for database-independent open-source middleware (similar to ArcSDE), that offers support for branched version management of relational data or it would be better to introduce such features in RDBMS itself?

I think I can try to dig deeper and propose some standard model or sublanguage to deal with Git-like versioning, but i don't know where to start.

  • From your brief requirements description, it doesn't sound like you need a branch strategy, and that a standard History Table and Version Column approach would satisfy the requirements. Perhaps you could elaborate on them. – Neil McGuigan Aug 17 '14 at 23:27

IMO this problem lacks sufficient generality to build standards or reusable solutions. It's really just a problem you solve with data modeling,

Something along the lines of:

CREATE TABLE DOC
(
  DOC_ID INT PRIMARY KEY,
  CURRENT_VERSION_ID INT,
  MODIFIED_AT DATETIME,
  MODIFIED_BY INT,
  CONSTRAINT FK_CURRENT_VERSION 
    FOREIGN KEY (DOC_ID, CURRENT_VERSION)
    REFERENCES DOC_VERSION(DOC_ID,VERSION_ID)

)


CREATE TABLE DOC_VERSION
(
  DOC_ID INT REFERENCES DOC,
  VERSION_ID INT,
  VERSION_OWNER INT,
  SOURCE_VERSION_ID INT,
  DOC_CONTENT NVARCHAR(MAX),
  CONSTRAINT PK_DOC_VERSION PRIMARY KEY (DOC_ID,VERSION_ID)
)

If the content being versioned is not stored as a blob, but is in separate tables, the pattern still holds. All the tables that store the versioned data need the VERSION_ID.

It would certainly be an interesting research project to explore all the different ways this can be done, and discuss options for conflict resolution, merging changes and sketch out what a general solution might look like.

Managing and keeping track on data modification (insert, update, delete) can be done quite easy using an audit table with trigger that keep track on every change.

While managing DDL (structure changes or procedure code changes) or DCL (grants on objects) do required version control.

There are some solutions in the market for database enforced change management which give you a version control to the database objects.

If I compare the RDBMS to a file-system, then it will be one directory stored in a shared drive which can be modified by all developers at the same time. Branching a database is the same process as branching in Git / SVN - create a new directory, which is equivalent to create a new schema / database. In addition, there is the shared situation that needs to be covered and to have an enforcement change policy to prevent code overwritten.

For more information you are welcome to read the "The Definitive Guide to Database Version Control" white paper

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.