Database schema’s need version control as much as application development. Database schema’s: tables, triggers, columns, data types, packages, functions, stored procedures, views, and so on, are not static and will change to accommodate the varying needs of your organization
If you work in a development team or a DBA team it is vital that nobody make ad-hoc changes and that all changes are documented. This boils down to one simple rule: “do NOT apply any scripts in your DB if they are not from source control.”
You can’t really enforce this rule to people, it simply comes down to discipline of all team members.
Here are some guidelines help you control your changes:
- Create a baseline schema. This is your starting point for changes to your database.
- Use a single authoritative source for your schema and code (read: source control). Your baseline schema will be revision “0″.
- Do not use a shared database server for development work. All members should run their own development database. This forces the team to use all code from source control. Developers cannot overwrite each others changes and development will be faster.
- For all changes other then a development environment; use a log for all changes! This can be a log file or a table in the database.
- Use atomic change scripts for all changes in your database. Use a date format for the filename. e.g.: change_2010_05_28-001.sql. Of course these scripts are in version control.
- All change scripts have an undo variant that will undo the change. undo_2010_05_28-001.sql.
- For all DDL changes other then in a development environment; Log DDL changes with a trigger and store these events in a table.
- Unit test your stored code! All changes made in your packages, functions or stored procedures are checked for validity and correctness.
How do you control your changes in your schema’s?