Read This Before You Redesign Your Database!
by: maloy@indusnet.co.in
Total views: 103
Word Count: 564
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 first name, middle name and last name 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.
About the Author
Mukul
Gupta is the CMO of Indus Net Technologies, an
Rating: Not yet rated
Login to vote