How far should you go with normalization?

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.

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.

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...