What you are hinting at is an underlying problem that every development
shop faces when more than one person needs to modify the same schema at
the same time.
I would ask the question "How do you manage changes for the non SQL
code in the system?" the answer to which *should* be the same in any
development team - "we use the source control system". Using source
control is obvious for C++, Java, HTML etc. but not for SQL code as it
has some unique requirements, not least of which being that you cannot
simply drop and replace a target database with a brand new one that has
no data in it!
What you need is to be able to simply modify the schema creation
scripts under source control to regulate multiple developers modifying
the same schema. This, for example, is the way you would work if you
were developing a brand new database that doesn't exist in production
yet. To release the latest version of the database you simply build a
brand new one from the create scripts and deliver that.
But what if you already have a production database? You can't just
throw it away, you need to carefully modify it to bring it up to the
new schema level.
This is where a tool called DB Ghost (
www.dbghost.com) comes in. It
will build a brand new database from the scripts in source control,
which verifies that no syntax or dependency problems exist. It then
uses that database as the source for a compare and upgrade of an
existing database i.e. production. This keeps all data in place and
means that you will have a target schema that absolutely matches a
defined, labelled set of create scripts under source control.
This is called the "DB Ghost Process" and it is simply the best change
management solution I have ever seen as it is the only tool that works
with source code.
The more people use the product, the more they love it as it makes so
much sense.
