Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a decent amount of data in a database. I have well formed tables and good relationships between them with some redundancy in my data. But how far should I go with normalization? Are there performance drawbacks to too much normalization?

share|improve this question
add comment

5 Answers

up vote 26 down vote accepted

You should go as far as you should, and no further. Of course. ~ The problem may be that this is a bit of an art, and it's why this isn't a pure science.

Our main product is an analysis and reporting system, and so in that regard, we have quite a few detail records. We initially had it designed with lots of joins on a common ID for some of the child records, but we found that if we denormalized a couple of fields we could cut out a LOT of joins and we could take away a lot of performance headaches.

But we only knew that because we 1) created a "normalized" design, 2) started using it, 3) profiled the actual performance after hundreds of millions of rows across dozens of tables.

The end story is that until we profiled we couldn't know for sure what was going to work for us. We liked the idea of normalizing so we could update more easily, but in the end actual performance was the deciding factor. That's my advice for you: Profile, profile, profile.

share|improve this answer
4  
art and not a science lends me to believe it's voodoo. Any references? –  abel Jan 11 '11 at 9:21
    
Good answer and have encountered a similar scenario myself. At the end of the day, it may be necessary to denormalise to improve performance depending on the usage and volume of data. I'm all for conforming to the principles of good relational DB design but in practice, performance may be more important. –  GShenanigan Jan 11 '11 at 11:06
2  
@Abel how about my anecdote in general? A profiler may be able to suggest rules for denormalization, but those rules come from a programmer from experience. All programming is an art. I'll find someone more famous who said the same thing when I get to a full keyboard later. –  jcolebrand Jan 11 '11 at 13:19
1  
@Abel oh well then all's in ('forgiven','pardoned') ;) :p –  jcolebrand Jan 11 '11 at 14:08
2  
@Fergus glad you liked it. I've always found anecdotes work best. –  jcolebrand Apr 4 '11 at 6:59
show 3 more comments

Normalization is a goal only when it supports your data model well enough to warrant it. It is meant to be a guide to allow growth, management and maintainability. Remember that the book on normalization, nor its writer are going to build or maintain your database or its application.

A good read on the subject of "too much normalization" is here.

And, yes there can be performance impacts to too much normalization. This would be in deeper table traversal to pick up things like status indicator tables when they have been pulled out to a separate table. Some will say this is usually negated in update speed (changing status text from "Good" to "GOOD" or something of the like) or in maintainability.

share|improve this answer
    
Great article @REW, thanks. –  Fergus Jan 11 '11 at 5:10
2  
Here's an additional good read on the subject and much more entertaining qntm.org/gay –  jcolebrand Jan 11 '11 at 5:15
add comment

I recommend reading the following appendix found in a few of Chris Date's more recent books:

Two Cheers For Normalization

Normalization is far from being a panacea, as we can easily see by considering what its goals are and how well it measures up against them...

I must make it clear that I don't want my comments in this section to be seen as any sort of attack. I believe firmly that anything less than a fully normalized design is strongly contraindicated...

share|improve this answer
add comment

I think it is equally important to look at the explicit added denormalisations, either added aggregate values or some fields from a master table copied to a detail copy.

The argument mostly being some performance argument.

If you do so enforce, that those fields are updated by triggers and it's up to the database to keep them consistent.

share|improve this answer
add comment

I totally agree with @drachenstern. When you're designing model for your application, you should normalize everything you can. But then you should profile the queries built over your model, especially those executed frequently.

My own experience: attributes that took two joins to reach (that means three tables joined) will be mostly a performance hog. And to make things worst, it is used in on line transactions. I denormalize the attribute, so it just need one join and asked the programmer to adjust their app for the query and updating the attribute. Now it works much better ...

In other word, you should balance normalization with performance.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.