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.

During development, should developers each have their own copy of the database to work on ? Or should there be a common shared development database ?

The former will give developers more flexibility. For ex., they can run their own regression tests during development and unit testing without worrying about corrupting the data for other developers. However, it is difficult to maintain for database adminstrators.

The latter is easier to maintain (less integration overheads as everyone's working on the same schema) but requires more effort to ensure data sanctity (developers should be careful and not make changes that will impact others).

share|improve this question
    
Why are you asking this as if it's either-or? Why are you excluding the most sensible choice, which is to do both? Why are you making this sound harder than it is? –  S.Lott Sep 21 '11 at 3:18
    
@S.Lott, because in many places, I have seen people argue that an individual schema for each developer is an overkill and causes more problems than it solves. What I want to know is if this is really true. –  Rahul Sep 21 '11 at 4:47
    
A false dichotomy (making an absolute either-or choice where no real distinction exists) is always wrong. Indeed, it's a common fallacy. It leads to numerous problems -- like this question which isn't a very good question because it creates a false dichotomy where this simple either-or choice does not really exist. –  S.Lott Sep 21 '11 at 9:59
    
What if the database you're working on is 3TB? Should everyone maintain their own copy locally? This issue should be tackled case by case basis. There's not a golden rule. –  sam yi Nov 15 '13 at 19:27

7 Answers 7

Well, there needs to be a central copy of the database to maintain the official schema.

For regression and unit testing, there should be available a sample database containing fictional, but reasonable data, and a blank database, both fully up to date with the latest schema from the master database. Scripts can be used to create these copies. The sample database is useful for testing, while the blank database is useful for new installations.

Ideally, unit tests should be designed using database transactions, so that tests which add, change or delete records can roll back the transaction to revert the database to its original state.

Many programs provide a mechanism for altering the database schema on-the-fly, which would be a requirement for deploying an update of the software to any customer who already has data in their database.

share|improve this answer

Each developer should have their own copy of the database for development. Period. Anything else gets in the way and can cause obstacles in the development environment. IMO, there is nothing worse than chasing down a bug for hours that was caused by a change somebody else made to some data you are using in your debugging.

As far as this being more work for the DBA's, you should handle all schema changes with scripts and automate the deployment of those scripts. There are tons of tools around to help with that (and many of them are free).

share|improve this answer
    
Lacking a shared database makes some kind of integration work difficult. What's wrong with "both"? –  S.Lott Sep 21 '11 at 12:26
    
I didn't say it but I believe both IS the best option. The question though, was about developers using a shared database for development so that's what I focused on (thus, forgetting to indicate both a shared and individual databases is the best option). –  Jeff Siver Sep 23 '11 at 0:18

Just an addition: There needs to be a central db. If one of the developer misses something, chances are high that the same problem (within the same db) would be caught by another developer. And yes, you need to make sure that the db transactions are ACID-ized.

share|improve this answer

The right answer is probably "both". This fits into the production/staging/development separation used in many development cycles.

  • Development/testing servers are available, as needed, to developers so that they can design new features and do regression testing with total freedom and impunity; nothing can break so bad that you can't just start over and be up and running again for the next test in no more time than a VM snapshot rollback.
  • Staging servers exist for the purpose of combining all of the features from all of the different developers and doing more extensive testing on real or reasonable models of real data. Having a separate staging system means that you can test out the deployment of the new features without risking real data; You can test on the staging machine until deployment becomes totally routine.
  • Production servers are usually off limits completely to developers. Knowledge learned on the staging systems means that IT staff can follow a piece by piece procedure. IT staff are often much better at keeping logs of what changes have been made to the production server than devs, and that's A-OK! At most, developers have just enough credentials on production servers to watch logs, but even that may be better left to IT.
share|improve this answer
    
The right answer to any false dichotomy like this one is always "both". Not 'probably "both"'. The either-or distinction in the question is a false distinction. This kind of stark choice doesn't really exist. –  S.Lott Sep 21 '11 at 10:01
    
@TokenMacGuy gave a good answer: the choice depends on the scenario. There is no need to get persnickety about "always", "probably" and the "false dichotomy". –  Angelo Sep 21 '11 at 16:20
    
@Angelo: The question is bad. It has a false dichotomy. An answer which falls into the trap created by a bad question is a weak answer. Changing one word makes a better answer and shows the problem with the question. Details matter. –  S.Lott Sep 23 '11 at 1:05

I'd say do whichever the developers want. Each application is different. Some may call for one, some may call for the other, some may call for both.

So ... if the programmer wants their own database, let them set one up (assuming space/resources are available, of course). If they'd rather use a shared development database for whatever reason, let them do that.

Basically, restricting developers to one or the other is not helping the development process.

share|improve this answer

I think developer's having separate databases causes more problems than it avoids. First, our databases are large, there is no way that they will fit on developer machines. Developing against a small subset is sure way to get code that won't work on prod (because it times out) which is a waste of time and effort on everyone's part.

Next, if my change is going to conflict with someone else's change, the sooner we find that out the better. That way neither one of us spends a lot of time devloping against a change that won't or can't happen and we can see that what we each are working on is realted and thus talk about how to integrate things as soon as possible. People see this as a minus in working with a shared database (because it is inconvenient), to me it is one of the biggest plusses (because it prevents redoing work in the long run).

We do have scratch database where people can play with concepts before they are ready to put them into the real databases.

share|improve this answer

The answer is that local database copies are good and should be done.

I think the real question behind this, though, is "what is the process for managing database changes"

Key things to consider are:

How local developers 'make' a change. How local db changes get pushed up. How central db changes get pushed down/out to developers.

Ruby in Rails for instance allows developers to do database migrations using ruby code, then the migration gets pushed to staging, testing and production and run there ('centrally') and pushed out to other developers (who pull it) to do the same.

Ay the end of the day what is critical for any system is communication, be it a standardized process or just emails about changes.

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.