I'm developing an app using Zend Framwork using Git for version control.
What is the best approach for updating the schema and the database when one of us makes an update to the db structure?
currently, we have to blow out the tables and recreate them manually to reflect the new updates.
I always keep track of all changes on two fronts:
I keep an empty version of the database and update the structure every time
I take care to write small scripts for every little change done. These are mostly DDL scripts (alter table add column etc), and are always kept in a well defined structure, with comments linking the statements to the tasks from Bugzilla/Jira whatever is used. Most of the time I make sure that these scripts are safe to be executed several times without errors.
Even if for some tasks I'm not the only one making changes, it's not difficult to synchronise our work, using svn.
Everything must be tested on at least one copy of the production environment.
Another way would be to use some database diff tools, these would compare the live database to your development database and generate scripts for changes to be done. I think Navicat has this feature. Some see this as a lot safer, well I think this is more chaotic. If you want a well done job, you do it yourself.
I would take a look at DBDeploy
dbdeploy is a Database Change Management tool. It’s for developers or DBAs who want to evolve their database design – or refactor their database – in a simple, controlled, flexible and frequent manner.
Theres also a good tutorial here on using DBDeploy alongside Phing (a PHP project build system or build tool based on Apache Ant)
A noteworthy implementation of a schema manager for ZF apps is Akrabat Db Schema Manager by Rob Allen. It uses migration scripts as suggested by @ceteras above. The link above includes code to use it with Zend_Tool though the library could be used on its own.