in a web application, how do keep the database structure up to date?

Question

If your data change after the application is deployed, how do you keep the database up to date?

I mean, you can add or remove table, thats a simple task. Altering an existing table can be trivial too. But if you change the structure often, how do you keep that under a control?

I used to keep a table with a current database version in database. Then every upgrade was an SQL file that did its work - create new table, add a column or move the data. Files were named after those versions - so if my upgrade script got the database version 10, it just took all files from 11.sql to N.sql and applied each one of them incrementing database version number at the same time.

This seems to be working all right, but I'm wondering - what's your strategy for such tasks?
Also this system doesn't seem perfect if I normalize a table in one "patch" and after that I denormalize it again for whatever reasons. Then it's done twice.

But, writing a complete upgrade script each time I change something seems painful, and error-prone. At least more then such atomic changes.

Also, I can expect that diffrent customers have diffrent database versions running at any time, so I really should have a way to go up from any point.

Answer 1

Personally I use a very similar process to what you have listed, I can see your argument about changing, but VERY rarely do I make a change, then change it back to the old way on a production site. In testing, yes, that happens, but in terms of an actual production site, I don't see it as a big deal.

Keeping individual version scripts, IMHO are not only good for deployment, but also good for providing items that can be checked into source control.