I'm building a Web/Web service application based on a number of (MySQL) databases. The databases are all built from externally derived data from dozens to hundreds of sources. My general plan is to have completely separate test, staging and production databases. The typical workflow is:
- Check to see if any of the data distributions have been updated. The update frequency varies from daily to yearly, and everything in between.
- If so, download the data, perform whatever transformations are required to turn it into SQL, and build a test database
- Run unit tests with the new data
- If everything passes, load the new data to a staging database
- Run tests again on the staging database (because I'm paranoid)
- Once a day, switch the production database to the staging database
All of the databases are read-only, once they're built. I very much like the idea of multiple versions of the database(s), but implementing this has me confused. Specifically:
- Because several of the databases have foreign key constraints, I can't just drop each table in turn and recreate it. I don't see any choice but to delete the whole database and rebuild it, but then I'd need to recreate all of the grants, and I don't want to have to run something with database admin privileges like this on a daily basis.
- I don't know how to swap the staging and production databases in a way that won't cost down-time.
I've looked for best practice in building databases within this general model, and come up dry. So, two questions:
- Am I on the right track with this general design, and
- What do I need to implement it?