Best practices team working with database [closed]

Question

What is the best practice for a team working on a same Database? Should developers use their Local database? Or a shared development database instance?

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

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 3

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 4

At Redgate we'd recommend that each developer is given their own instance, as sandboxing ensures that developers don't tread on each other's toes. However, there are pros and cons with both models.

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.