Should we have separate database instance for each developer?

Question

What is the best way for developing a database based application? We can have two approaches.

1. One common database for all the developers.
2. Separate database for all the developers.

What are the pros and cons of each? And which one is better way?

Edit: More then one developer is supposed to update the database and we already have SqlExpress 2005 on each developer machine.

Edit: Most of us are suggesting a common database. However if one of the dev has modified the code and database schema . He has not committed the code changes but the schema changes has gone to the common database. Will it not possibly break the other developers code.

Answer 1

Both -

I like a single database that changes are tested on before going live, or going to a 'formal' test environment. This is your developer's sanity check; it stays up to date with the live system and it makes sure they always consider each others changes. The rule should be that changes don't go on here if they might break something else.

A database per developer is great (even essential) when more than one developer is making updates. It allows them all the development flexibility they want without breaking things for other developers.

The key is to have a process for moving database changes from development through to your live system, and stick to your process.

Answer 2

Shared database

• Simpler
• Less cases of "It works on my machine".
• Forces integration
• Issues are found quickly (fail fast)

Individual databases

• Never affect other developers, but this is also a bad thing, in continuous integration

We use a shared development database and it works out nicely. Our schema rarely changes in a way that makes it backwards incompatible, but occasionally a design change will occur before we go live, and we simply ask the other developers to update.

We do have separate development application (web) servers, but they share the same database. Our developers do have the option to use their own database, as they know how to set this up, and will do that on occasion, but only temporarily. The norm, for us, is to share the database.