Best practices team working with database

Question

What is better for team working on DB? Is it Local database instance for each developer or shared database?

Answer 1

In my experience a team should have (at least) one shared database for integraton.

During development each team member should have an independed database otherwise changes of database schema could hinder other team members. These instances could also be on a centralized server.

Answer 2

From experience, a shared database is better. Our code used to break all the time because someone added a column on their local database, then uploaded their new source code to SVN. Everyone else's implementation would then break until they figured out what had changed in the database.

I would have a shared database for development. We had one or two dev databases too for miscellaneous testing.

Answer 3

I can only talk about the way the team I'm in currently works, which fits our needs well enough:

We have one central data model script that updates any database automatically to the latest schema version. Developers check in changes to this script together with changes to the source code (single commit on the same repository). The nightly builds update a central database copy, followed by a batch of automated tests on that database, and the human QA team also uses this same database the next day for all their testing.

We don't allow schema changes on the central database instance any other way than via the integration builds. For developing the schema change script, changes are developed on a separate database instance, either on the central server, or locally (depending on personal preference).

Answer 4

I don't think it depends at all. Each environment should have it's own database instance. Personally, I would never reccommend that everyone on the team works on the same copy of the source, and I view the database code and instance the same way.

If you are having problems with missing database changes, this is a symptom of a different development process issue. It would be analagous to forgetting to add a code file to source control.

Jeff Atwood has a pretty good article on source controlling database code.

Different developers supposedly work on different issues - how do you avoid stepping on other people's toes while unit testing?

I would absolutely advocate an integration/test environment, which is updated via a Continuous Integration process. This environment often serves as a litmus test for your deployment procedure as well.

Answer 5

At Red Gate we'd recommend that each developer is given their own instance, as sandboxing ensures that developers don't tread on each others' toes. However, there are pros and cons with both models, which are outlined further in this article:

http://www.red-gate.com/products/SQL_Source_Control/evaluation_center/database_development_models.htm

In our experience talking to database developers, roughly half of database development is performed on a shared environment, and half on a dedicated per-developer environment.