Having different database instances for different phases of project?

Question

Is it recommended? What you you all think about this?

Or anyonw have better approach for managing different phases of project with source code control / database control care to share their views?

Answer 1

If by different phases you mean development phase and production phase then it is good by all means. But I don't think different DB instances during development phase are a good practice - is there any reason for that in your project?

Answer 2

My recommendation is to keep the database schema definition under source control, using the same SCM repository and branches as the rest of the code in the project. That way it's easier to keep the database in sync with the application that uses it. It would be embarrassing for the application to query a table that doesn't exist.

Since development typically is going on in different branches of source control (e.g. version 1.0 maintenance, version 1.1 development, and version 2.0 early development), you need a database instance per branch. This allows you to modify the database design for version 1.1 and 2.0 as needed, without interfering with the stability of maintenance work on version 1.0.

You definitely need a separate database instance for production versus testing. I worked on an app that sent notification emails to users. I didn't want users to be confused by spurious emails sent by my test instance. In my test data, I changed all the email addresses to mock addresses at my own domain, so I could verify they were being sent correctly, as many times as I needed to, without bothering anyone. I couldn't do that if I were using the same database for test and production.

Ideally, each developer on your team should have their own environment, including all components of the system (such as the database). That way each developer can work independently, able to make changes and invoke debuggers and such, without interfering with the work of anyone else on the team.

On the other hand, this may not be practical. Setting up a complete duplicate system for each developer may be too expensive. Compare the cost of this setup work to the potential for gains in productivity, since developers won't have to coordinate testing with each other as much.

Answer 3

The most sensible meaning I can ascribe to your question is: What phases of a project want their own shared database instances? The standard answer (which I generally concur with, all else being equal) is development (for the current build), testing (structurally static and refreshable for regession testing), and production.

More might be added for design development vs. implementation development; several kinds of testing; etc.

Of course each developer should be able to clone any of these for unshared purposes.

Answer 4

Typically I've have multiple DBs

• One for Support (or more if you have multiple versions).
• One for regression testing
• One for unit testing/experimentation by QA
• One for Development/Experimentation by the Devs (sometimes multiple instances, but I prefer one - that way we're sure everyone is coding to the same schema).