How do you keep your metadata under version control?

Question

We have a database that persist our metadata and data.

Our metadata is produced buy a dedicated team, using a Web application on the development server, and is a critical part of our application.

Then the customer generates data according to this metadata.

We already version the database schema, and all schema change. The next step is to put our metadata under version control.

Naive solution

A naive solution would be to dump all the metadata, and commit it under version control before generating the corresponding packages. Since it's a dump, it can easily be restored. But there is probably a better way, like an incremental solution (only version diffs).

Text dumps

Another solution is to export all metadata tables in text format (like XML), and then version those text files. But then you have to find a way to reimport them.

So, is your metadata under version control? Why? How?

Answer 1

Non Niave solution:-

Version control your mata data build scripts.

I.E. the database schema drop/create and the SQL "INSERTS" should both be placed under version control.

The advantage of this approach is thet the Schema and Insert scripts really are source code. Version controls systems are designed for source code so they play well with this method. In addition its pretty much self contained and can be easily ported to other systems.

If your meta data is devleoped interactivley there are several utilities that will allow you to export your database as an insert script.

Answer 2

K. Scott Allen has a series of excellent blog posts about this subject:

• Versioning Databases - The Baseline
• Versioning Databases - Change Scripts
• Versioning Databases - Views, Stored Procedures, and the Like
• Versioning Databases - Branching and Merging

Answer 3

Something to consider would be the tools available by Embarcadero. ErStudio for example provides the ability to version control against your entire database model.