Chapter 10 Denormalizing a PDM

Overview

Usually normalization consists in eliminating redundancy and inconsistent dependencies between tables in order to create a functional and efficient relational database.

Denormalization is the process of putting one fact in numerous places, in order to speed data retrieval. The only valid reason for denormalizing a relational design is to enhance performance. However, there are several indicators which will help to identify systems and tables which are potential denormalization candidates. These are:

Critical queries rely upon data from more than one table
Many calculations need to be applied to one or many columns before queries can be successfully answered
Tables need to be accessed in different ways by different users during the same timeframe
Certain columns are queried a large percentage of the time

When deciding whether to denormalize, you need to analyze the data access requirements of the applications in your environment and their actual performance characteristics. Often, good indexing and other solutions solve many performance problems rather than denormalization.

Denormalization may be accomplished in several ways:

Horizontal partitioning is used to divide a table into multiple tables containing the same columns but fewer rows
Vertical partitioning is used to divide a table into multiple tables containing the same number of rows but fewer columns
Table collapsing is used to merge tables in order to eliminate the join between them
Column denormalization is used to repeat a column in tables in order to avoid creating a join between tables

The following sections explain these different denormalization techniques.