Database development in teams

I’ve never learned anywhere in university, courses or from colleagues about how to develop a database in teams. Or lets say: Never the way that satisfied me. I hated it. It left an important part of development completely blank.

Thinking about how you use version control for all of your code to reproduce previous commits, branch it, fix it, merge it – this never happened anywhere I looked at development for databases.

What I’ve seen people do:

  1. Developer use a shared database. That was the most common scenario. There was one commonly hosted database for development. I don’t even want to think about what happens when in a previous commit/version a bug is found. But I also imagine the parallel development horrible. Adding might be fine – think about your colleague deleting or changing columns while you still work on your user story.
  2. Everyone has an own copy of their database – also pretty common. You just get a copy, do your stuff, upload it in the later shared database. Still horrible – still in fear the other colleague will overwrite your changes.
  3. Having scripts and jobs which copy stuff together set it up whatever. It might work somehow but in my eyes it’s far from perfect.

What I want to have:

One version controled database with the power of using mighty merging tools and one IDE which supports application and database development.

Let’s say we work in Eclipse to develope our awesome Java MVC web application with a relational MySQL database. In this case I see two folders in the project folder:

  1. Application – this is where Java code, Maven-stuff, HTML, JPS, whatever is saved.
  2. Database – this is where everything for the database lies. One file, or maybe multiple files which “outsource” e.g. views.

There is still one more point: Data.

Data will mostly be different from your own development environment to the testing environment or the live environment. You put data for personal testing purposes in your own DB, in the testing environment there is standardized testing data and there is no sense about putting a mighty super admin which you used in your testing environment with an commonly known password in your live environment. There should be the option to define specific datasets for specific environments – this is what you can save as well in the database folder. The IDE should support such datasets. There should also be at least one unversioned dataset for personal testing purposes. Copying, merging etc. different datasets in an own personal not version-controlled dataset should be possible.

How you merge those databases is somehow weird.

Nevertheless for a lot of these questions there is one great article I found: http://blog.codinghorror.com/get-your-database-under-version-control/

Some questions or wishes I have are still unanswered.