|We don't bite newbies here... much|
As I see it this is a general database based project problem but I have not seen much discussion on it here or elsewhere (please correct if wrong). For the record I am using Postgres 7.4.
It is also an standard upgrade problem - What to do when you go from one version to the next version which changes the datbase structure.
You have more chanllenges when doing it on a live system where the database can't go down (or may be can go down only for few minutes).
I agree that each change must be versioned so that, for example you can go from version 1 to 3 (skipping version 2).
One interesting problem to consider is:
Say a customer has version 1.
you made a table change (new column X) in version 2
You made another table change in version 3 (new column Y
Now your version 3 SQL will have only 'add column Y'
To upgrade from version 1 to 3, you need to combine both changes or upgrade to version 2 first and then upgrade to version 3