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.