How to "upgrade" the database in real world?

Question

My company have develop a web application using php + mysql. The system can display a product's original price and discount price to the user. If you haven't logined, you get the original price, if you loginned , you get the discount price. It is pretty easy to understand.

But my company want more features in the system, it want to display different prices base on different user. For example, user A is a golden parnter, he can get 50% off. User B is a silver parnter, only have 30 % off. But this logic is not prepare in the original system, so I need to add some attribute in the database, at least a user type in this example. Is there any recommendation on how to merge current database to my new version of database. Also, all the data should preserver, and the server should works 24/7. (within stop the database)

Is it possible to do so? Also , any recommend for future maintaince advice? Thz u.

Answer 1

I would recommend writing a tool to run SQL queries to your databases incrementally. Much like Rails migrations.

In the system I am currently working on, we have such tool written in python, we name our scripts something like 000000_somename.sql, where the 0s is the revision number in our SCM (subversion), and the tool is run as part of development/testing and finally deploying to production.

This has the benefit of being able to go back in time in terms of database changes, much like in code (if you use a source code version control tool) too.