I have a website that I am designing that uses a bunch of PHP scripts to generate pages for a website using an underlying MySQL database.

I have the PHP scripts under CVS, and using Apache and mod_rewrite I am able to access different versions of the website - each with their own set of PHP scripts from the CVS project.

The problem is this: I would like to version the MySQL database as well.

This means that there would be multiple MySQL databases, with slightly varying schemas on the server. Each PHP version of the website would point to a corresponding MySQL database for its data. Multiple PHP versions could point to the same DB, as long as each PHP version has SQL statements matching the schema of the correspoinding DB.

http://forums.databasejournal.com/clear.gif

One version of the DB would be a live version of the site (v1 for example, above). Users accessing the site would causes changes to the data in the DB (not the schema, of course).

The second version of the DB (v2) would need to basically be a slave replication server, using DB v1 as the master. This will allow DB v2 to be up-to-date with all of the user updates that appear in DV v1. DB v2 is the development copy. As the design matures, schema changes will be made to DB v2. At some point, DB v2 will be made the "live" server, essentially taking the role of DB v1.

As long as fields are only added to tables in DB v2 to modify the schema, the developer can easily just feed the updates from DB v1 to v2, to keep the user data current.

The complicated part is when schema changes need to be made to v2 where fields are deleted, renamed, moved, etc.

What is needed, basically, is a system that can track the ALTER TABLE statements, and appropriately translate the UPDATE statements from DB v1 to the new schema of DB v2. The developer could specify non-obvious and more complicated changes in some sort of syntax in order to clarify the changes to the underlying schema - thus allowing the generation of the proper relational algebraic (SQL) statements from the original UPDATE statements.

Thanks!