Technical Details

I generally agree with this article, except for the fact that developers should be applying schema patches in the course of regular development.

Managing change with your database yields a great many benefits. Since the schema change scripts are in source control, you can recreate your database as it looked at any point in time. Is a customer reporting a bug on build 3.1.5.6723? Pull the source code tagged or labeled with that version number and run the baseline, then all schema change scripts included in the tag. You now have the same database and have a much better chance to recreate the bug. Also, changes move from development to test, and ultimately into production in a consistent, orderly, and reproducible manner.

This is a bad precedent and if you get lazy and don’t apply the schema changes to the original schema you’re soon going to run into problems where the only source for table definition will be the database itself, a very bad situation for developers (that’s why you have version control in the first place, tag your schema changes) Yes you need these schema migration files for production systems and they of course need to be tested, but developers should always be building their development database from source and never applying schema updates unless they are testing them.

I recently finished working at a place where the DBA had set up such a scheme when I left there were 22 schema updates (that spanned 7 years) that had to be applied, in some cases the original table definition barely resembled the updated version and the updates were scattered through 22 different files truly brain damaging.

So in conclusion keep all your DB artifacts in separate files, for me that includes tables, triggers(rarely use them), indexes, stored procs, … I would go as far as keeping the schema changes in separate files based on the artifact they are responsible for updating, all relatively easy to script. ALWAYS UPDATE YOUR BASELINE with the schema changes.