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? What practices or tools do you recommend? Is it feasible for complex databases to quit on a graphical tool and to code everything with SQL scripts? If so, what are good practices here? What are good working models to maintain these two repositories and to coordinate the developers?