6

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).

4
  • 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, 2011 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, 2011 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, 2011 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, 2013 at 19:27

7 Answers 7

10

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.
3
  • 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, 2011 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, 2011 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, 2011 at 1:05
8

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).

2
  • Lacking a shared database makes some kind of integration work difficult. What's wrong with "both"?
    – S.Lott
    Sep 21, 2011 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, 2011 at 0:18
4

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.

2
  • Your "databases are large"? You mean your production databases are large? You don't need all that to dev and test locally. Create just enough data to do what you need and reuse that. I hate hearing this excuse.
    – cottsak
    May 26, 2016 at 6:49
  • On your second point (with the exception of an applications infancy where the two or three folks are working very close together on the same elements and thus are already having very high resolution conversations in order to coordinate) normally, folks are working on different things and if you're work is tasked into small enough pieces and you integrate often the likelihood of this "stepping on each others toes" is very low especially when using a good version controlled scripting tool. Also a lame excuse.
    – cottsak
    May 26, 2016 at 6:51
2

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.

2

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.

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.