Denormalizing Tables and Columns

Database normalization consists in eliminating redundancy and inconsistent dependencies between tables. While normalization is generally considered the goal of database design, denormalization, the deliberate duplication of certain data in order to speed data retrieval, may be appropriate in certain cases:

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 how to implement these denormalization techniques in PowerDesigner.

Creating Horizontal Partitions
Horizontal partitioning consists in segmenting a table into multiple tables each containing a subset of rows and the same columns as the partitioned table in order to optimize data retrieval. You can use any column, including primary keys, as partitioning criteria.
Creating Vertical Partitions
Vertical partitioning consists in segmenting a table into multiple tables each containing a subset of columns and the same number of rows as the partitioned table. The partition tables share the same primary key.
Creating Table Collapsings
Table collapsing consists in merging tables into a single table in order to eliminate joins and to improve query performance.
Denormalizing Columns
You can denormalize columns to eliminate frequent joins using column denormalization.
Denormalization Object Properties
A denormalization transformation object is automatically created when you partition a table using the Horizontal or Vertical Partitioning Wizard or collapse tables with the Table Collapsing Wizard.
Example: Intermodel Generation and Horizontal Partitions
When you update a PDM generated from another model, any horizontal partitioning is preserved.
Example: Intermodel Generation and Vertical Partitions
When you generate in update mode a PDM from a PDM, a CDM or an OOM, vertical partitioning is preserved.
Removing Partitionings and Table Collapsings
You can remove partitionings or table collapsings and either keep or remove the associated tables.

Parent topic: Tables (PDM)