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?
5 Answers
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.
answered Jan 11, 2011 at 5:29
-
5
-
3@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, 2011 at 13:19
-
1
-
2@Fergus glad you liked it. I've always found anecdotes work best.– jcolebrand ♦Apr 4, 2011 at 6:59
-
2@abel - 'An art is a science with more than 7 degrees of freedom'. Beyond a certain level of complexity exhaustive approaches to a problem become infeasible. At that point heuristic approaches based on experience are most effective. Sadly, in the field of computing that level of complexity is quite easy to achieve on anything but trivial software systems. Apr 11, 2012 at 10:37
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 neither 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 a performance impact from 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.
-
2Here's an additional good read on the subject and much more entertaining qntm.org/gay– jcolebrand ♦Jan 11, 2011 at 5:15
I recommend reading the following appendix found in a few of Chris Date's more recent books:
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...
answered Apr 11, 2012 at 9:15
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.
I totally agree with @jcolebrand. 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.
