As for the getting it right at the design stage I agree, but it is not always possible or even desirable. I happen to have very few changes to make but a change is a change and requires respect - hence the OP.

Changes can occur for lots of reasons, usually they are used to add features. For example say I am ( and I really am ) designing a small simple web shop. After a few months of trading it turns out that discounts would be a good thing for customers spending over a certain amount. To accomodate this the database (and code) needs to be changed. This sort of thing cannot always be spotted at the initial design stage and it would be impossible to take into account all the possible future features that are needed.

In summary: Yes, getting it right at the start is a good thing, but so is making changes based on lessons learnt.

After a few months of trading it turns out that discounts would be a good thing for customers spending over a certain amount. To accomodate this the database (and code) needs to be changed.

Actually, I would think that for adding such functionality you would only need to add to a DB, not actually modify the existing structure. (I dont consider table additions to be a modification.) Perhaps your tables weren't sufficiently normalized? ;-) (Code changes would for sure be required.)

Anyway, snarkyness aside, my approach with stuff like this tends to be to write a perl script that does the DB modification via SQL. I test it on copies of the production DB, and then once happy and the code is approved for production run it on the production DB. I like to use the perl script to run the SQL because I find that its usually the easiest way to ensure newly added fields have the appropriate values assigned to them. For instance, ill use alter table to add a field as nullable, walk the table updating each record so it has the correct value, then alter table again to set it not-nullable, and perhaps add an index on the new columns.

Anyway, good luck.



---
demerphq


First they ignore you, then they laugh at you, then they fight you, then you win.
-- Gandhi

I've been developing a web site with a friend for about the past 8 months. We've gone through a few major schema changes. This has been the approach we've followed when moving to a new schema and it has worked very well.

When we're ready to go live on a new schema, I'll compare the old schema to the new one and see where the tables differ. From that, I generate SQL statements to copy the data from the old schema to the new schema (I use quite a bit of perl code through all of this). We're using SQL Server so we can copy data directly from one schema to another with SQL statements. If I couldn't do it this way, I'd probably use perl to manage the copying.

A few days before we go live, we'll create a new database, load the new schema, test the conversion scripts and do testing on the new site. If all is well, we'll create another new database and do it all again to go live.

Most of our schema changes are due to new features being added, not design problems (yeah, that's what they all say :-).

At my last job, we tried using 'ALTER TABLE' commands to maintain a database. This didn't work too well. Sometimes the changes were too involved to use 'ALTER TABLE' and tables would have to get recreated. This presented problems with tracking foreign key constraints. Besides, if you get something wrong in this process, you only have a database dump to fall back on (you do backup your database before changing the schema, right?). With the method described above, all of the old data is still intact in the old schema.