Professional SQL Server™ 2005 Programming

7.8. De-Normalization

I'm going to keep this relatively short, since this tends to get into fairly advanced concepts, but remember not to get carried away with the normalization of your data.

As I stated early in this chapter, normalization is one of those things that database designers sometimes wear like a cross. It's somehow turned into a religion for them, and they begin normalizing data for the sake of normalization rather than for good things it does to their database. Here are a couple of things to think about in this regard:

• If declaring a computed column or storing some derived data is going to allow you to run a report more effectively, then by all means put it in. Just remember to take into account the benefit versus the risk. (For example, what if your "summary" data gets out of sync with the data it can be derived from? How will you determine that it happened, and how will you fix it if it does happen?)

• Sometimes, by including just one (or more) de-normalized column in a table, you can eliminate or significantly cut down the number of joins necessary to retrieve information. Watch for these scenarios — they actually come up reasonably frequently. I've dealt with situations where adding one column to one commonly used base table cut a nine-table join down to just three, and cut the query time by about 90 percent in the process.

• If you are keeping historical data — data that will largely go unchanged and is just used for reporting — then the integrity issue becomes a ...