Version Control – Database Modeling and Software Development

Here is the framework

We have an SQL database in which our database model lives in. On the other side, there is a bunch of code using and filling that database.

Here is what we want

• We want to put both, the database model and the software code, under version control to be able to go forth and back within
  different versions.
• Since our database model becomes nasty, we want a graphical tool that assists us with the logical database modelling and that creates our SQL DLL scripts

This post is about the question how to get both of these requirements satisfied at the same time. Personally, I tend to skip the requirement for the graphical tool and to simply write our hole database model within SQL scripts and add them to git. However, its very hard to maintain the model as the model becomes more complicated and (easy) logical changes can be huge code changes.

Here is our current "solution"

Our software code is under version control with git. Our logical
database model on the other hand is maintained with PowerDesigner, which brings (with some limitations) its own version control. At the end of the day, we generate our SQL DDL statements from the logical model.

Here are our problems with the current situation

Eventhough the code is under version control, the development of the code is somehow disconnected from the development of the database model. A change of an object in the logical database model (like the change of a column name) is
not visible in git. Even if we checkout a certain version in git, an additional mapping is needed that tells us which version of the
logical database model I have to use. Moreover, branches in
git cannot be mapped to branches within PowerDesigner, which makes development chaotic.

Here are some unification-ideas we came up with

Exporting the logical model from PowerDesigner as (XML) and put it under
version control in git.

Here, I know which version of the code relates to which version of the
PowerDesigner, since I simply can import the XML back. However, the
git diffs on the XML are useless and merging to different logical models in git is hopeless. Thus, there has to be a merge within git and within PowerDesigner. Moreover, I have to
export the model to an XML for every commit that involves changes on the database model (which are unfortunately quite frequent) and which
slows down the development process a lot. Moreover, bugs are hard to
track, because I have to search my code with git and my logical model
within Powerdesigner seperately.

Put the database scripts generated by PowerDesigner under version
control.

Here, the git diffs have a meaning, and I can relate changes of the
code precisely to changes of the logical model. But, I cannot go
back a version within PowerDesigner just with the generated database scripts. So, again, I need to export the XML
of the model as well and I get the same problems than above.

Here are my questions

• How do you connect the world of code, the world of logical database
  modeling, and the world of version-control?

• Is it feasible for complex databases to quit on a graphical tool and to code everything with SQL scripts?

• What are good working models to maintain these two repositories and to coordinate the developers?