A legacy database has grown organically over time and is a mess. I need to normalize it in the best possible manner without affecting all of the application code that touches it (minimize impact on software development group).
If you have done this before, I would like to hear how you accomplished this and what you learned.
Here are a couple of resources I found that provide some technologies.
Normalization through views http://www.sqlservercentral.com/articles/Normalization/64428/
Instead of Insert on a view http://msdn.microsoft.com/en-us/library/ms175089.aspx
asked Aug 19 '10 at 05:26 AM in Default
I follow the old saying Normalize until it hurts the performance and de-normalize until it works!
While designing the DB keep performance in mind. Bad logical database design results in bad physical database design, and generally results in poor database performance.
I feel the difficulty depends on your existing structure of your DB. If you are not following at-least Ist NF then, its really tougher to bring it on First NF (Especially if you are storing more than 1 value on a single column). You may need to create ad-hoc queries, stored procedures, may be computed columns etc. to pull the data to new normalized table. Even you may need to create SSIS Packages to pull the data at some point you may need to synchronize the data on normalized table with the Denormalized one.
I recommend simple approch
First design your tables atleast up to 3NF, establish Relationship, do partition your tables, design proper indexes. if required create some indexed views
After first phase design the scripts to pull data(I say test the scripts properly) from the denormalized tables. schedule it on job to run on off-peak time.
Cyborg has a good answer (+1), but I think I may have a little to add of value.
First, if your requirement is to normalize without affecting your application code, then whether or not this is even possible depends on a few things. First, are your applications going through stored procedures at least for the updates/inserts/deletes?
If they are, then you can probably achieve what you want without touching the true application code. First, create a normalized data structure and bring over the data. Then rewrite your stored procedures so they work with the newly normalized structure. If any of your programs are using tables directly for reads, you can replace those tables with views that present the information in exactly the same way. The application would never need to know.
If they work directly with the tables or work through an ORM, then you may have a choice of normalizing and changing the application code or doing only very mindor changes. Normalizing involves rearranging columns and very often involves creating more tables (as a general rule of thumb, the higher the level of normalization you are going for, the more tables you must have). So if the application code is working on those tables directly, then it is impossible.
I have in the past had some success in at least coming close to 3NF from a database that grew organically before I was put on the project. But doing so involved substantial changes to the applications, and it was a relatively small application. Achieving it for a major application or a database upon which numerous applications depended would be challenging and would require substantial testing.
answered Aug 19 '10 at 09:54 AM