In most line of business application there are two major types of artifacts involved : the database and the application. Sometime there are two types of developers, but mostly that is not the case. The same pure smuck that built an entity inside the application will usually write the database and UI code for it.
Well generally I don't think thats wrong. I strongly belive in cross functional teams where each developer can work with equal skill on each application tier. When you work in the outsourcing industry as I do, you quickly came to appreciate those team members who seem to be able to do evertyhing: database modeling, stored procedures, domain objects, html and css layouts, documentation, testing and so on.
The major problem with the approach of one developer doing both the application and the database work is that that developer approaches the database work in the same way as he approaches the development of a typical application. But those two types of artifacts are not the same, they behave differently during each phase of the product life cyle.
The application code is fairly easy to modify, extend and delete where the database code is always resistant to change, and it grows more resistant as the product and its database grows. Why is that?
The database is composed of three different types of artifacts:
Data structure is the logical representation of the types of data stored in the database and their respectiove relations. We call them tables.
The programmed logic is what we say to the database it should do with the data that goes in and out of a table. We call this : stored procedures, functions, triggers, constraints etc. To most developers this is the closest thing to real development they will find in a database project.
The data, well, there is not much to say about that. This is the meat and bones of what a database is to most people. The reason d'etre of a database. Users data, application or system data stored for eternity in a database for all to see and use. Most databases store data about them selves in special databases and tables. Figure that.
Well the thing is with databases that the data stored inside them is the most valuable thing in a line of business project. A developer can frag the entire application code and it wouldn't be even as worse if he fragged the data stored in a database. So, losing or modifying data in a database is not a good thing.
As a database (and its accompanied application code) is being developed the volume of useful, production data inside it grows. Each change to the database structure becomes much hardere, since you cannot loose data (thats why most people try to nail the database structure before starting the development process) , and it gets even worse when the database goes live to live free in the wild of an enterprise IT infrastructure. If you frag the database there you can kiss your ass, head and small fingers good bye because angry people in business suits will descend apon your small little you like vultures on a helpless white rabbit with its back pawns broken. Its that bad.
So working with the database is hard and risky.
The underlying fact with the database is that its always changing. New data is added inside it, changed, deleted. The structure is change, new tables are being added each day. Even the programmed logic changes from time to time. It is always changing regardless of it being in development or in production. While the nice and good application code changes quite rapidly and easily during development but its damn impossible to change when its compiled in an executable and shipped to remote production server in the prairie.
So what to do when you have a line of business product to ship on a rapid development track with database and application work done in parallel by the same people. Well , its simple.
Stop making changes in the database manually. Write scripts.
Repeatable, stable , data saving, scripts.
Then , respect the database. It is not your run of mill .NET application. It requires different development practices.
In database work you done have the write, compile, test life cycle of application development. Once you loose data it is gone, once you mess thing its hard to go back.
Think of a new development process for working with the database. It will repay you ten fold in virgins.