As business evolves, the information that they store and process also changes and that means the underlying database structures that have been holding well for many years needs to be changed as well. This are even more complicated when you have redesign an 'in use' system.

Here is a step by step guide on what needs to be done:

Step 1: Draw the structure of the new database on Paper Based on the new business requirements, you need to draw the structure of the new database on paper. We often use MS Access at this stage because it has a got a 'Documenter' tool which generates a real professional output in a relatively short time.

Step 2: Analyze Existing Scripts / Code
Now you need to analyze the existing code to see how they are using the current database structure and how this usage will change based on the revised structure. For instance, if you are trying to split a long table into two separate tables, then all previous 'Select *' queries on that table will be useful. Thus, all the existing code will have to be rewritten.

Step 3: Create a migration plan
You need to create a migration plan. Leaving the tech stuff aside, this basically means how you are going to transfer the data from the old data into the new database. For this you will have a write a script of some kind. This stage is very critical because you might have to adjust your new database. For instance: If in the old database you have customers table which had a name column. Now, if the revised database has firstname, middlename and lastname column then I can bet your database redesign dreams will end here as you can't migrate the data between the two versions unless your plan includes manual typing.

Step 4: Create the new database
Now you get to design the new database structure as it should be implemented unless you have done in the very first step itself.

Step 5: Test the Migration Plan with a Demo Script
You now need to test the migration plan on a sub-set of 'old' database to ensure that the data can be migrated as you planned.

Step 6: Adjust the code
Now its time to edit the code! You need to go line by line and change all queries which are affected by the revised design.

Step 7: Perform the Migration
The day you have been waiting for has finally arrived; you get to perform the migration which means you upload the new code and execute the migration script.

Last Step: Test
All said and done, done forget to test the whole system once again to ensure that things are working as expected.
Of course, you should never play with the live database. With all those GUI tools available in the market, it's real tempting and easy to make changes on the live system. Always work on a backup copy and perform the migration on actual system once the dummy system has been tested properly.