Using SVN with a MySQL database ran by xamp - yes or no? (and how?)

Question

For my current PHP/MySQL project (over a group of 4 to 5 team members), we are using this setup: each developer codes and test on his localhost running xamp, and upload to a test server via SVN.

One question that I have now is how to synchronize the MySQL database? I may have added a new table to project and the PHP code references to it, so my other team members would need to access that table for my code (once they got it through SVN) to work.

We are not always working in the same office all the time, so having a LAN and a MySQL server in the office is not feasible. So I am toying with 2 solutions

1. Setup a test DB online, and have all the coders will reference to that, even when coding from localhost. Downside: you can't test if you happen not have internet access.

2. Somehow sync the localhost copy of MySQL DB. Is that kind of silly? And if I do consider this, how do I do it? (which folder do I add to SVN?)

(I guess a related question is how to automatically update the live MySQL DB from the testing DB, regardless if it is on a remote server or hosted locally via xamp. Any advice regarding that would be welcomed!)

Answer 1

It's perfectly reasonable to maintain a database schema in source control -- in fact, most database-driven products do just that. I have no idea how your repository is laid out, but as long as you don't have your PHP code directly under the root, you'll be fine. If you do have PHP code under the root, I strongly suggest an svn mv into its own directory.

Storing data in source control is a little more problematic. If you require that data to initialize the database for use, then sure, it makes sense. Same with test data. If you're trying to store live data, not so much (that's why you have a database, after all). I don't know how MySQL manages database backups, but I'm assuming there's a more compact format than a long SQL script (Oracle, for example, as exp format).

---

To give a real-world example, here's the approach taken by a product that I worked on.

• The DDL to create the current database schema was under source-control, and was part of the release.
• The SQL (DML) to insert initial values into that database was also under source control.
• For every release, there was a script to update the database from the previous release. This script altered / added tables, and inserted/deleted rows.
• Test programs assumed a "clean" database, and were responsible for inserting their own data.
• As this was a product, any post-initialization data was added by the customer.

I started working at the company when the product was 5 years old, and left 4 years later. I never tried from a version 1 release, but I know that a version 2.x release could be upgraded all the way to a version 4.0 release (there were typically dot-releases every 4 months).