x

Process to Normalize a Denormalized database

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
more ▼

asked Aug 19 '10 at 05:26 AM in Default

sysaux gravatar image

sysaux
1 2 3 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Aug 19 '10 at 06:02 AM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Aug 19 '10 at 09:54 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x13

asked: Aug 19 '10 at 05:26 AM

Seen: 1375 times

Last Updated: Aug 19 '10 at 05:26 AM