I was having a discussion with a colleague earlier today about the
development environment.

I am seeking opinions, and to avoid slanting the input one way or the other,
I am not even going to label which opinion I hold.

The question on the table is about the database location for the DEVELOPMENT
environment.

The development team would typically consist of 4-5 people.  With the
potential to be up to 10 developers on a single team.

1.  Opinion A

Each developer is either given his/her own database to work with/on during
development.
The database is isolated per developer during development.
While developing, any data in the database is there because a single
developer has put it there (inserted it) (or updated it or deleted it).
The developer is free to drop (and usually re-add) tables, udf's (user
defined functions), usp's (user stored procedures), triggers, views, as
needed.
If the developer ever needed to "start from scratch" (for the Northwind
metaphor, something like deleting all customers, all orders, and all order
details), he/she would be free to do this without group permission.

After a particuliar feature is developed, all code (dotnet) is checked in,
along with any .sql (tsql,ddl) changes as well.
At certain intervals, code is compiled and deployed, database is redeployed
on a staging server for (usually) QA testing.
(The QA setup is on db servers, web server...which the developers don't
really get involved with as far as development goes)

The practical setup would be 1 of 2 scenarios.
Scenario 1 would be each developer is given a copy of Sql Server Developer
Edition.  Thus he/she would be "sa" on his/her own development machine.

Scenario 2 would be each developer is given an (isolated for the single
developer) database on a common (development) Sql Server.  Thus instead of a
single common Northwind database ( Northwind as an example), there would be
databases deployed for each developer:
NorthwindBob
NorthwindEd
NorthwindSue
NorthwindJoe
NorthwindJane
database.

2.  Opinion B
All developers on a single team share a common development database.
While developing, data is seen by all team members on this single team
database.
In order to provide some order, team members will be given rules for
segmenting data while developing and testing.
The Northwind methaphor would be something along the lines of:
Bob, you can deal with customers with the LastName of A,B,C.
Ed, you can deal with customers with LastName of D,E,F.
Sue, you can deal with customers with LastName of G,H,I.
Joe, you can deal with customers with LastName of J,K,L.
Jane, you can deal with customers with LastName of M,N,O.

If the developer ever needed to "start from scratch" (for the Northwind
metaphor, something like deleting all customers, all orders, and all order
details), he/she would either have to stay in their segment (only delete
"his/her" customers of ABCor DEF, etc) or get group-approval from all other
team members to do a "clear all".

.........
Other information:
The db is not a simple thing like the Northwind metaphor.  It has 80
tables....and only one of those is a lookup/code table.  (Aka, the majority
of the tables are not lookup tables like "States", "ZipCodes"...these are
consolidated into 1 table...with a CodeCategory(Key) to put the lookups into
separate categories.  The majority of tables are their to allow the system
to do what it needs to do.

To ease initial setup...there are helper scripts to populate an empty
database with some baseline data.  In the Northwind metaphor, this would be
the equivalent of adding Products,Suppliers,etc.
The db would be empty of Customers,Orders,OrderDetails.....but the more
"lookup" type data would be contained in a single script.
(Aka, when the developer needs a fresh database to work with, they don't
have to manually go in and "Create Products", "Create Supplies").

The Northwind methaphor is of course a metaphor, we are not making the best
darned NorthwindManager in the world.

There are times when developers are focused on certain aspects of the
project.
Sometimes its purely logic, and being able to make sure the system works.
And being able to add/edit/delete customers/orders/order details (in the
Northwind metaphor) is what is being focused on.
Other times, there are scalability things going on.  Like inserting
(1,000,000 customers, 2,000,000 Orders, 4,000,000 OrderDetails) into the
database, and then seeing how fast (tweaking tsql, indexes, usp's) an
application user can bring up the Orders/OrderDetails for one particuliar
customer. (Please reread OpinionA and OpinionB at this point as well from
above).

The team members are routinely involved with table/usp/udf/views creation.
While I know in some environments the dba is more heavily involved in these
areas, our dba's are more maintenance type dba's.  Our developers pretty
much are involved with the entire process of the application development.
What is right or wrong here, I don't know.  But this is the reality of the
current environment.
Aka, an opinion along the lines of "that's the dba's job" won't really help
out here.  Please feel free to express that opinion, I'm just relaying the
reality that it won't actually go-down that way in my current environment.

The best metaphor I can come up with (without taking 3 pages to explain what
this system does) would be a HelpDeskTicket system.
Roll with me here...this is a metaphor........
There is workflow type stuff, where if the HelpDeskTicketWorker doesn't know
how to answer a question, it can be promoted to a hierarchy of advanced
HelpDeskTicketWorkers.
The HelpDeskTicketWorker will do N number of items at a time (usually
10)....and he/she is allowed to review the 10 items before actually
(officially) submitting them.  Aka, while the 10 items are in this temporal
state...the HelpDeskTicketWorker can edit their suggestions and these edits
are updated to the database.
Another "rule" would be that something like 50% (configurable value) of all
HelpDeskTicket items are actually processed TWICE to ensure quality
assurance.  So when a HelpDeskTicketWorker submits the 10 items....5 of them
will actually be marked as "Still Incomplete" so another
HelpDeskTicketWorker will do them again (without any knowledge it has
already been addressed the first time).
Advanced HelpDeskTicket workers will actually come in and look at the 2
responses from 2 different people and give them "Ok" or "The FirstGuy is
Nuts" type evaluations.

I mention the HelpDeskTicket metaphor to paint a small picture of the
system.  It is not a HelpDeskTicket system....so please don't lose focus and
start picking holes at the HelpDeskTicket metaphor.

......

While still learning and getting better, the team does strive to incorporate
NUnit testing.
Right now, to build deployment scripts (.dll and .sql), there are some poor
man's versions of MSBuild, but the eventual goal (this summer?) is to get to
MSBuild and automate alot of this stuff.

......

The big question is the database setup environment for a multiple developer
team.
Aka, what do you think about OpinionA, OpinionB....or perhaps your own
OpinionC.
I've tried to paint an objective representation of both opinions.
Scenario1 and Scenario2 (under OpinionA) aren't really the focus of my
post....but if you have a small comment on that...that's ok as well.
OpinionA and OpinionB are the crux of the issue.

Please express your opinion...alternate setup ideas are welcome as well.

If you don't actually work in a team environment the majority of time, and
are expressing an opinion....please at least acknowledge that.
I will value your opinion, but to be honest...working in a team environment
is something that needs a little first-hand experience to empathize with I
think.
..........

As always...thanks for any input.

..