Data migrations for one systems to the next are always a bit tricky. The level of involvement can be minimal. The small things like already knowing where certian tables are located or even using a sproc/view that can get you the requested data in an instance make some migrations as easy as pie.
On the other ther hand if you dont have help from the other party(whatever the reason may be) this is where the process can get somewhat time consuming. If this is the case you are stuck with days (possibly weeks) of data analysis before you can even start to understand the complexity in which they have structured their db. While specifc table names that denote what a table does or the data that it holds can come in handy it can also be misleading. A good db will have a lot of comments when the structure is complex. This can help tremendously. If this is not the case then you can just start at the sprocs(stored procedures).
Starting with the sprocs will allow you to see the naming conventions they use and also the manner in which they pull data themselves. This can be a great starting poing for that reason. Once you have a somewhat small grasp on whats going on or even if they dont have any sprocs, the next place you go is into the Views.
The views will be your best friend through it all. The views will allow you to see the matter in which the data is connected. The point of a view is usually for the end user to use. In this case it will be the data they want to see in the way in which they have requested. Using the create sql for the view you should be able to back track to the actual tables that are a part of it and see how everything is linked together. Once you have those links it should be simple to start creating your own sql statements to pull the required data from the database.
You can also use the functions to see if there is anything to help you decipher the structure also.
If once you are done you have the info you were looking for then you have successfully reverse engineered the database. (It helps to some how be able to verify the data in which ou have retrieved from the database.)
13 Responses to Deciphering the Database