There are better ways than this, especially in Ruby on Rails, but here's a manual idea for managing database changes during development.
- All database change scripts be put in _db directory
- Three kinds of scripts:
- create_db.sql, which drops all existing database objects and creates the original schema.
- alter_db_yyyymmddThhmmss.sql, which changes the schema.
- update_db_yyyymmddThhmmss.sql, which changes the data.
- With this method, the entire schema can be recreated by running the scripts in order. (update_db is obviously a problem.)
- Scripts should run within transactions.