Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Denormalization Strategies Expand / Collapse
Author
Message
Posted Sunday, March 14, 2010 2:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:18 AM
Points: 22, Visits: 141
Comments posted to this topic are about the item Denormalization Strategies
Post #882621
Posted Sunday, March 14, 2010 10:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, March 14, 2010 11:07 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
Posted Monday, March 15, 2010 2:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,908, Visits: 1,834
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
Newbie on www.simple-talk.com
Post #882762
Posted Monday, March 15, 2010 5:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 19, 2014 1:46 PM
Points: 2,604, Visits: 572
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
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #882800
Posted Monday, March 15, 2010 5:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Normalize 'til it hurts...de-normalize* 'til it works!

* appropriately




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882802
Posted Monday, March 15, 2010 5:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, March 15, 2010 6:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 19, 2014 1:46 PM
Points: 2,604, Visits: 572
Paul White (3/15/2010)
Normalize 'til it hurts...de-normalize* 'til it works!

* appropriately


good point! :)


/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #882857
Posted Monday, March 15, 2010 6:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 1, 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
Posted Monday, March 15, 2010 7:18 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse