- What Does It Mean to Normalize a Database?
- Steps to Normalize Your Data Model
- Denormalize Data—When Does It Make Sense to Break the Rules?
- Normalization Applied—Review the TEB Database and Refine the Design
- What You Have Learned
This chapter is from the book
This chapter is from the book
This chapter is from the book
Denormalize Data—When Does It Make Sense to Break the Rules?
Consider the case of the city, state, and citystatezip tables. On one hand, these tables conform to the third normal form. On the other hand, every time you need to write a query to extract customer data, you will need to join data from four tables. Whenever you normalize data, you always have to balance the competing goals of reducing data redundancy with the extra work that will be created when needing to extract data from the database. Most of the time, elements like city, state, and ZIP Code are carried redundantly. Consider the case of trying to assign a city to a customer but the city has not been defined in the city table. You need to maintain the city table first. Now, what would have taken only one operation now takes two. This is not to say that a valid business reason may not exist to divide the city, state, and ZIP Code information into multiple tables. In this case, it makes sense to break the rules and denormalize.
The rules of normalization are important ones to follow. At the same time, it is important to recognize that situations do arise where it makes sense to break the rules. If you do break the rules, be sure you have an important reason for doing so. Reducing the complexity of queries and improving query performance are good reasons to denormalize. Because you are breaking the rule, it should be an exception, not the norm. Therefore, if you find yourself continually denormalizing, take a moment to scrutinize what you are doing. Chances are, you are misapplying the concept of denormalization.