How Do You Sync Database Schemas?

Rob Sweet asks: "I recently got started coding for a PHP front end to RRDTool. Right now, there are only two developers but we get the impression that once a protocol is in place, we'll have several more. The question has been posed: We can use CVS to keep our code synchronized but how do we go about keeping our database schemas synchronized? The obvious answers involve using mysqldump to keep updated table creation scripts in CVS but I'm wondering if there isn't a better way..." At the very least, a file containing a list of schema changes would be necessary, but what about programs that can take two schemas, look at the differences, and return the commands necessary to make the one mirror the other?

Comments

you suggested it yourself

[frankske]

we put a mysqldump (with a couple of options) in the cvs! We considered other options, but it's by far the easiest!

Have you read their website

[Chacham]

Either you haven't RTMed, or you didn't explain very well. The documentation has it here. Just make both masters and both slaves, and hope you don't trample on each other.

Now that you have the key to complete disaster, I will warn you since you obviously don't know this. This is a stupid idea. Hmm... that not harsh enough. If you were working for me, I'd reconsider your employment if you came up with this crazy idea.

You should never, ever, ever, ever, have more than one person working on the same schema while coding! Either the database will be driven by your code, which is a quick way to denormalize everthing, and wreck havok, or more than one person will drive the design, and will denormalize and wreck havoc.

Only one person (or group) should make decisions on a schema, and it should be done, *before* any coding is done. The database structure will lend structure to the program itself.

I was a DBA for Oracle at a small company. You wouldn't believe what those idiots (Andrew, you listening? :-)) wanted to do, and did when I went on vacation! Instead of coding properly, they added tables to make it easier. And then they wonder why the DB slows down on their queries. Or they want to know a concrete method (query or idea) of encapsulating all data of a certain type, and wonder why I can't do it, since they left so many holes in the system.

The database should be designed to handle the system. And that it much more important than coding. Both because of speed and structural reasons. The only time the database should be changed, is when there was a mistake in the original design, or the project it is for has changed dramatically.

So, their is a way to do it, but its on par with logging in as root all the time, so things are easier. Don't do it.