Denormalization Strategies

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Timothy Claason » Denormalization Strategies

47 posts, Page 1 of 5

1 2 3 4 5 »»»

Denormalization Strategies

Rate Topic

Display Mode

Topic Options

Author

Message

timclaason

Posted Sunday, March 14, 2010 2:50 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:56 AM
Points: 22, Visits: 140

Comments posted to this topic are about the item Denormalization Strategies

Post #882621

louis 40040

Posted Sunday, March 14, 2010 10:16 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 15, 2010 3:08 PM
Points: 2, Visits: 5

Great article. Makes me feel justified in all that time I spend going about the detail. Thanks.

Post #882698

Paul White

Posted Sunday, March 14, 2010 11:07 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,990, Visits: 10,545

The article would have benefited from code examples, either in-line or in an attached file.

The only type of denormalization considered is full redundancy: storing the results of a complex join in a single table. No discussion of the disadvantages of doing this, no analysis of where any performance benefits might come from, aside from join overhead. The design presented seems to be nothing more than a naiive indexed view over the full result of the eliminated join.

There are many more useful types of denormalization which were not mentioned: maintaining aggregates with indexed view or even triggers for example.

Overall, I rated it average - 3 stars.

Paul

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #882716

David.Poole

Posted Monday, March 15, 2010 2:54 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 2,750, Visits: 1,410

If you denormalise the database where data it maintained you add to the cost of data maintenance tasks and you run the increased risk of being hit by bugs due to trying to maintain data in more than one place.

If you have separation between where you maintain data and where you present data then denormalisation of the latter makes a lot of sense from a performance perspective.

Obviously a denormlised database is going to require more work when a change is required because data changes end up requiring schema changes

LinkedIn Profile

Post #882762

hakan.winther

Posted Monday, March 15, 2010 5:01 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:31 AM
Points: 2,226, Visits: 438

Remember that there is no such thing as a free lunch! There are always a cost for everything. Denormalization Will cost you disk, maintenance and sometimes the overhead of updating indexes (or the lack of it) may cost you performance. A well designed database with correct indexes, correct sql statements on a well dimensioned hardware is probably the right aproach for 80 percent of all Case. And yes for some Cases denormalization is the right aproach, but do not implement it everywhere just because you can.

Håkan Winther

/Håkan Winther
MCITP:Database Developer 2008

Post #882800

Paul White

Posted Monday, March 15, 2010 5:14 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,990, Visits: 10,545

Normalize 'til it hurts...de-normalize* 'til it works!

* appropriately

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #882802

louis 40040

Posted Monday, March 15, 2010 5:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 15, 2010 3:08 PM
Points: 2, Visits: 5

I maintain, in any event, that if things are tied together by their ID's they are tied together by pure luck, and the only way to get there is to bore down to the most basic unit. * insert obligatory conundrum here *

Post #882810

hakan.winther

Posted Monday, March 15, 2010 6:28 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:31 AM
Points: 2,226, Visits: 438

Paul White (3/15/2010)

Normalize 'til it hurts...de-normalize* 'til it works!

* appropriately

good point! :)

/Håkan Winther
MCITP:Database Developer 2008

Post #882857

Gilles Willard

Posted Monday, March 15, 2010 6:59 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 01, 2012 6:53 AM
Points: 50, Visits: 180

Maybe this is due to my lack of experience, or the fact that it's been several years since I last studied database normalization, but:

It seems to me that what Timothy is proposing is simply adding a few FKs to his database design to cut down on the JOINs required to get at some data.

How is that denormalizing?

I went back to my database design textbook (as well as the paper I wrote then on denormalization...)and could not figure out which form (1NF, 2NF or 3NF) was being violated?

Also, and please forgive my fussiness with this but I have teachers in my family going back three generations, Timothy's article, while being interesting, could have used a quick run through spell-checking. This is not just a "purity" thing: it is a little jarring to find typos or spelling mistakes in a technical article while you're concentrating on understanding the author's point, something like stumbling over a rock while on a good walk.

Post #882891

Paul White

Posted Monday, March 15, 2010 7:18 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,990, Visits: 10,545

Gilles Willard (3/15/2010)

It seems to me that what Timothy is proposing is simply adding a few FKs to his database design to cut down on the JOINs required to get at some data. How is that denormalizing?

This is why I think code examples would have helped. My assumption is that he is de-normalizing the entire lower structure - putting the result of the JOINs into the parent table. That is how I read it anyway. I agree about the rocks by the way.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #882908

« Prev Topic | Next Topic »

47 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions