Database upgrade process - DB version migration

[Walter w]

Hi!

I'm not sure if I have choose right forum for this topic, but let me describe my issue:

I'm having a software - that uses MySQL as database. I have around 80 tables in datbase - software is under developement so DB get constantly updated (small changes like new table or column ... etc).Some part of this software is desktop application - and some is website installed along with each installation (PHP+MySQL+Apache). Now the software is at the stage of first release. I'm using InstallAware as installator - to copy the files, fill register, install MySQL, binaries...etc... To install MySQL I just run mysql.sql that contains schema of database. Now - I need to process software upgrade. Lets imagine I will have to provide small fixes or full upgrade package to already installed instances at client side. This process will be probably automatic - software will connect to our centralized server & download upgrade package. Upgrading files / register or config files is easy - but I will have to probably upgrade DB each time for those small changes.

Now the question is - what is the best way to handle this? What if I will have to make a release each 2 weeks - and each might have small changes to DB structure.

-----------------------

Could you give me idea what is common way to handle this type of upgrade?

I'm thinking for each release I will have to prepare upgrading / convertion script - that will change DB structure / data.

But what if user will have to upgrade older release - to newest jumping over few releases, like having release 1, 2, 3, 4. Should I prepare script for:
1->2 & 1->3 & 1->4
2->3 & 2->4
3->4

So for each combination I have diffrent script? I dont like this solution - because after a while it become complex.

Or maybe I just prepare upgrade scripts for:
1->2
2->3
3->4

And once user want to upgrade 1->4 I run each of this scripts one by one?

Or another way - making a full DUMP of data - then delete database, install new version - and at the end import dump. I'm assuming that new columns will be just empty. But it's not going to work when I delete some columns, right?

-----------------

Or am I making this too complex - and there's some simpler solutions? I would like to automate this process as much as possible ;)

Please give any comments,
Thank you ;)