Denormalization Strategies

Question

Post reply

Denormalization Strategies

timclaason

SSC-Addicted

Points: 486
More actions
March 14, 2010 at 2:50 pm

#386497

Comments posted to this topic are about the item Denormalization Strategies

Viewing 15 posts - 1 through 15 (of 45 total)

You must be logged in to reply to this topic. Login to reply

louis 40040 SSC Journeyman Points: 96 More actions · Answer 1

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

David.Poole SSC Guru Points: 76000 More actions · Answer 3

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

SQLWinther SSCertifiable Points: 6294 More actions · Answer 4

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

Paul White SSC Guru Points: 150467 More actions · Answer 5

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

* appropriately

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

louis 40040 SSC Journeyman Points: 96 More actions · Answer 6

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 *

SQLWinther SSCertifiable Points: 6294 More actions · Answer 7

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

Gilles Willard Right there with Babe Points: 718 More actions · Answer 8

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.

Paul White SSC Guru Points: 150467 More actions · Answer 9

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

James Stephens Mr or Mrs. 500 Points: 517 More actions · Answer 10

We use a de-normalized data-mart--I may be using the term loosely but it's esssentially "a separate place for up-to-yesterday production data" where semi-trained users can put together queries on our core data without getting into trouble with join syntax and without us investing in a huge Crystal Reports development effort.

It makes a lot of sense for this because it's not the main production transactional db, we can get away with a read-optimized box with a few huge wide-flat tables rather than many tables with joins.

Disk space is cheap. Why make the same Y2k mistake and add unnecessary complexity to a data mart to save what amounts to in our case, about $100 worth of disk space.

90% of the most commonly asked reports are no longer dumped on the desk of our IT staff--a small investment in simple query syntax training for end users solved most of the issues.

Granted, we're small and simple but a similar approach this could be scaled.

In addition, we have some data-specific issue where this helps. For example, Customer 123 might be "Bobs Widgets" today, but later he changes it to "Bob & Sons Widgets". I understand about temporal tables but if you've ever seen what joining a temporal table does to an execution plan (not to mention 8 or 10 temporal tables) then that's something for which I'm more than willing to add a redundant field.

--Jim

Jaji03 SSC Enthusiast Points: 139 More actions · Answer 11

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?

I had the same assumption...that is until he mentioned the fact that he decreased the number of tables. It would have been nice to have a diagram with the table design showing the suggested changes.

Paul White SSC Guru Points: 150467 More actions · Answer 12

Jim,

Yes. Data warehouses are a totally different kettle.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 13

Paul White (3/15/2010)
Jim,
Yes. Data warehouses are a totally different kettle.

It's normal for denormalization to be present in a data warehouse.

(Seriously, there was no pun intended.)

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 14

Alvin Ramard (3/15/2010)
(Seriously, there was no pun intended.)

Given your track record for bad puns, Alvin, I have my doubts :laugh:

Benefit of the doubt. 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi