Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a central read-only MySQL database that contains geo data, etc. It is used by various projects. As part of a refactoring, I am thinking about setting up a system for versioning the database. The rough idea so far:

  • The current database is version 1. It is available at:

    1.database.example.com:3306/database_1
    
  • When in the future the database structure is changed in an incompatible way, then the version number is increased to 2, and the new database is made available at:

    2.database.example.com:3306/database_2
    

    That allows updating projects one by one from interfacing with 1.database.example.com to interfacing with 2.database.example.com. Due to the different domain name, it is possible to use a different database server for 2.database.example.com, even one not based on MySQL.

Does that make sense? Is there a standard or recommended way for “versioning” a read-only database?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

One must separate: What do you want to "version"? The interface or the data?

Versioning the interface

If you only want to version the interface, you should (must) manage one single base of data and provide it in different formats at different interfaces. Otherwise you will run into consistency issues, if your data should change (and even geo data changes -- the rarity of changes makes it even more vulnerable to consistency issues, because this is done so infrequent that humans tend to do mistakes).

In this case I would use one single database and VIEWs for different interfaces. This is harder to setup, but saves headache in the long run.

An alternative would be to replicate the data periodically by a cronjob, which might be a waste of resources if this changes very infrequently.

You could also implement a script and make somehow sure it is always executed if some data changes. (Vulnerable to human errors this is.)

Versioning the data

If you want to version everything including the data, your approach seems fine to me. Although it might be more efficient to just use another database instead of a completely different server instance.

share|improve this answer
    
For now I decided to keep the first database version simply as example.com:3306/database. Putting version numbers everywhere, even if not needed now, feels like overengineering. Only in the documentation I suggested the possibility of running several versions in parallel, starting with example.com:3306/database_2 (or on a different DB server). –  feklee Feb 25 at 15:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.