Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We are currently usign a roll-forward approach to DB changes, akin to Migrations, where each developer creates and checks in a script that promotes the latest version of the DB to a new state. Problems arise when multiple developers concurrently work on non-trivial tasks and end up making changes to the DB that interfere with each other. The 'non-trivial' bit is significant because if the tasks take long enough, and if DB changes occurr early enough in the cycle, neither dev ends up being aware of the other's changes, and we end up with either a DB merge nightmare (preferred case) or an inadvertently broken database.

Can these situations be easily avoided? Are there database refactoring strategies that effectively handle the scenario of multiple developers actively changing the schema?

If it matters, we use SQL Server.

share|improve this question

6 Answers 6

Martin Fowler and Pramod Sadalage have written an excellent article on this subject:

Evolutionary Database Design

I suggest you follow his advice, e.g. that a dedicated DBA reviews the changes and merges them into the database master, while each developer can test things on his very own database.

In my own company, we've also found that Database Comparison Tools can greatly aid this process, because they can create update scripts automatically, e.g. to update production environments.

share|improve this answer
2  
Also, there's a whole book about the topic you might wanna check it: amazon.com/Refactoring-Databases-Evolutionary-Database-Design/… –  Shady M. Najib Jul 8 '11 at 8:55
    
+1 We use this exact same technique, and it works. –  Justin Shield Jul 8 '11 at 11:55

Establish a Reviewing Process. Everyone who implemented a migration has to review the next migrations. Check in a single migration documentation file, in which everyone who modifies the database has to provide a synopsis of the new migration. If two people implement changes they need to be aware of, the resulting merge conflict in your CVS will alarm them.

share|improve this answer

http://www.liquibase.org

Quote from the front page:

You never develop code without version control, why do you develop your database without it?

Liquibase is an open source (Apache 2.0 Licensed), database-independent library for tracking, managing and applying database changes. It is built on a simple premise: All database changes are stored in a human readable yet trackable form and checked into source control.

share|improve this answer
    
The database migration scripts that the OP currently uses may be in source control, so how does Liquibase solve the problem? –  Mike Partridge Sep 11 at 12:09

The only solution I've found is to have a functional layer between the DB and the application. Data needs in the application require abstraction from the DB schema and vice versa, lest a change of schema become massive project surgery. This is not the same thing as using an ORM.

If the DB schema should be changed then the change must be accompanied by changes in the functional request layer that pulls/pushes data. Likewise, changes in the data representation within the application must be accompanied by changes in the data layer that pulls/pushes data.

What this approach adds in terms of coding requirements in data abstraction call code it saves in massive, rewrite-the-world project-shaking changes. In particular, it prevents those project-wide changes from turning into feature/mission creep and putting your team in an impossible situation.

An additional benefit to this approach is that you can implement such a layer now and in increments without getting too wild up front. For example, you can write a 1-for-1 table query layer that separates the calling code from the DB query, and replace the previous in-code queries (or ORM or DB framework calls) with calls to these new functions that accept and return exactly whatever you are dealing with right now. This is easy. Now if you need a DB change, do it with an accompanying change to the data layer code so it accepts and returns whatever it does now but deals with the DB change gracefully. You can then change the calling code itself if this makes sense.

Sometimes you discover something interesting about non-trivial data in this process -- namely that object-to-table translations are not 1-for-1 and that the shape of the data in code can never match its shape in a relational DB, but that a relational DB is the most reasonable way (sometimes the only way) to store the data if it might ever directly serve more than a single application.

share|improve this answer

Consider using a distributed version control system like git.

Create one repository for the database change scripts and use a workflow such as 'you must fetch the latest, merge your changes in, commit and push and only after pushing and seeing no rejection (because others pushed) should you run your script on the actual shared db server. Until then each developer can have a local copy, usually pared down to some test data.

share|improve this answer

Daily stand-up meetings prevent this problem.

http://martinfowler.com/articles/itsNotJustStandingUp.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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