Database Version Checklist: Hot or Not?

Question

Based on reading on the Internet, stack overflows and mainly these db version control articles that were related to coding horror, I took a hit on writing a plan for version control of the database of the 8 year old php mysql website.

Database Version Control plan - Create a db as the "Master Database" - Create a table db_version (id, script_name, version_number, author, comment, date_ran) - Create baseline script for schema+core data that creates this db from scratch, run this on Master Db - Create a "test data" script to load any db with working data - Modifications to the master db are ONLY to be made through the db versioning process - Ensure everyone developing against the Master Db has a local db created by the baseline script - Procedures for commiting and updating from the Master Db - Master Db Commit - Perform a schema diff between your local db and the master db - Perform a data diff on core data between your local db and master db - If there are changes in either or both cases, combine these changes into an update script - Collect the data to be added to a new row in db_version table, and add an insert for this into the script - new version number = latest master db version number +1 - author - comment - The script must be named as changeScript_V.sql where V is the latest master db version +1 - Run the script against the master db - If the script executed succesfully, add it to the svn repository - Add the new db_version record to your local db_version table - Update from Master Db - Update your local svn checkout to have all the latest change scripts available - compares your local db_version table to the master db_version table to determine which change scripts to run - Run the required change scripts in order against your local db, which will also update your local db_version table 

My first question is: does this sound right?
My second question is that the fixing process looks a bit more complicated than once a day. Is there a way to reliably automate it? Or do I not need to change databases often to make this matter?

Answer 1

Looking at your suggestions, this does not look like it is possible and practical. I worked for a company where we used more than 1 thousand tables per database (a very complex system), and it all worked like this:

• Have one person responsible for the DB (call him DBPerson) - every script / db change must go through it. This will avoid any unnecessary changes, and some “ignore” the problems (for example, if someone translates the index in order to better fulfill his query, hello, maybe he will destroy other people, maybe someone will create a table that will completely redundant and unnecessary, etc ...). This will keep the db clean and efficient. Even if it seems that this is too much work for one guy (or his deputy), in fact this is not so: usually dB usually rarely changes.
• Each script must pass validation through DBPerson
• When the script is approved, DBPerson assigns the number and puts it in the "update" / svn (...) folder with the corresponding numbering (as you suggested, incremental numbers, for example).
• Then, if you have some continuous integration, the script receives and updates db (if you do not have continuous integration, do it manually).
• Do not store the entire script database, with all the data in the script. Instead, keep the actual database. If you have decision branches, each branch has its own database, or you can always have update scripts shared for each branch so that you can roll back / forward another branch. But I really recommend having a separate db for each branch.
• Each database always has default data (intact) - for the needs of unit tests, regression tests, etc. Whenever you run tests, make them on a copy of this database. You can even carry out daily cleaning of test databases using the main one (if necessary, of course).

In such an environment, you will have several versions of the database:

• The developer base (local) is the one that the developer uses to test his work. He can always copy Master or Test Master.
• The primary database — one that has all the defaults — can be half empty if you are redeploying for new clients.
• The Test Master database is the main database populated with test data. Any scripts that you run into the Masters, you also worked here.
• The testing database in progress - copied from the Test Master and used for testing - is overwritten before any new test.
• If you have branches (a similar database with a small difference for each client), then you will have the same as above for each branch ...

You will probably have to make changes to this situation to suit your situation, but in any case, I believe that saving the text version of the create script for the entire database is incorrect in terms of maintainability, merging, updating, etc.