How we do version control for our database
iviva.com
iviva.com
Generation of the deployment script using a diff has so many holes. Renames can be misinterpreted as drop/create. It falls apart with data to insert/deleted. Ordering can't always be calculated.
If your versions are a straight chronological sequence it's straight forward to advance from any past version to any future version. Run each patch till you arrive at the desired version.
If you branch out in multiple forks it can be a little tricky. I've never had this situation, but I would treat each branch as a separate chronological sequence of patches. Unfortunately merging doesn't work with "patch" style development, so this could be a duplication nightmare. I guess nothing is perfect.
If the customer can make unrestricted modifications to their DB (unknown to you), you're out of luck. Versions are no longer under your control and generating with a diff is the way to go. But in that case the diff seems even more dangerous. Will it destroy their custom modifications?
So customers don't modify the database directly. However we do have custom changes for certain customers - those all go into the data dictionary.
The tool is very careful not to drop any columns or do any destructive alterations without manual intervention.
The other problem we have is our platform supports multiple applications and not all customers use all applications.
For example, one customer may only be interested in Room Booking and Visitor Management.
Another would be interested in Room Booking, Work Requests and Asset Management.
Some customers may have a custom version of the Work Requests system with slightly different database structures. That further complicates the situation.
The infrastructure we have in place is far from perfect, but so far has neatly done the job for us.
Thanks for your feedback!