Database Versioning Approaches

Life requires databases (as well as source code) to be changed from time to time. While source code changes are managed using version control systems there is no de-facto standards on how to track the database changes in schema and data.

Following the list of articles about issues of updating databases:
Achieving an Automated Approach and Version Control
Managing Database Changes
Evolutionary Database Design
Is Your Database Under Version Control?
11 Tools for Database Versioning
Ruby on Rails Migrations Explained

Following are the considerations regarding relations of code, data and real life (subject domain) entities:

• The code and DB schema are dead (means they are not changed by subject domain).


• All the data is live (it reflects subject domain).


• Some data (reference tables, constants) may be considered quasi-dead since it less likely to be changed by subject domain.


• There is no straightforward way to restore DB snapshot w/o loosing recent updates.


• Any live data can be represented by snapshot and series of updates (transactions).


• While snapshot is merely database full backup, the updates are not the DBMS low-level transaction but rather high-level log of interactions with subject domain. So called BusinessLogic-level transactions.


• Stored BL-level transactions then can be applied to the snapshot or new empty database to bring it in sync with real world at the same time the system processes the new BL-transactions. The best is that can be done w/o shutting down the operations (only interrupting current sessions if applicable).

Conclusion: If you want to untie your mission-critical real-time project from actual implementation of data model/platform for easy migration then you need to implement BL transactional DBMS upon SQL-based convention DBMS.