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 sometimes be more desirable.

PowerDesigner supports denormalization through:

Horizontal partitioning - dividing a table into multiple tables containing the same columns but fewer rows.
Vertical partitioning - dividing a table into multiple tables containing the same number of rows but fewer columns.
Table collapsing - merging tables in order to eliminate the join between them.
Column denormalization - repeating a column in multiple tables in order to avoid creating a join between them.

Horizontal and vertical partitioning involve tradeoffs in terms of performance and complexity. Though they can improve query response time and accelerate data backup and recovery, they require additional joins and unions to retrieve data from multiple tables, more complex queries to determine which table contains the requested data, and additional metadata to describe the partitioned table. Column denormalization can simplify queries but requires more maintenance and storage space as data is duplicated.

When deciding whether to denormalize, you should analyze the data access requirements of the applications in your environment and their actual performance characteristics. Often, good indexing and other solutions may more effectively address performance problems. Denormalization may be appropriate when:

Critical queries rely upon data from more than one table.
Many calculations need to be applied to columns before queries can be successfully answered.
Tables need to be accessed in different ways by different kinds of users simultaneously.
Certain columns are queried extremely frequently.

Horizontal Partitions
Horizontal partitioning consists in segmenting a table into multiple tables each containing a subset of rows and the same columns in order to optimize data retrieval. You can use any column, including primary keys, as partitioning criteria.
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.
Table Collapsings
Table collapsing consists in merging tables in order to eliminate joins and to improve query performance. You can collapse tables related to each other with a reference or tables with identical primary keys.
Column Denormalization
Column denormalization consists in replicating columns from one table to another to reduce the number of joins needed for frequently called queries. Though it can provide improved performance, column denormalization requires more maintenance and disk space as the data in the replicated column is stored twice.
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. To access the property sheet of this object, select Model > Transformations to open the List of Transformations, select the appropriate denormalization, and then click the Properties tool.
Removing Partitionings and Table Collapsings
You can remove partitionings or table collapsings and either keep or remove the associated tables.

Parent topic: Tables (PDM)