Creating table collapsings

Chapter 4 Building Physical Diagrams

Creating table collapsings

Table collapsing consists in merging tables into a single table in order to eliminate joins and to improve query performance.

The generated table gathers the columns of the merged tables. All incoming and outgoing references to the input tables are preserved in the resulting table. When the collapsed tables are related by references, the following occurs:

The parent column of the join is no longer needed, thus removed
The columns of the parent table are duplicated
The foreign keys of the children are removed, but their columns are preserved in the resulting table

Example

Tables Customer and Order are linked together.

To optimize data retrieval in the database, you collapse both tables into a single table to eliminate the join. The result is a single table (with 2 synonym symbols) with the primary key of the child table:

Table Collapsing Wizard

The Table Collapsing Wizard lets you merge multiple tables into a single table. You can collapse tables related to each other with a reference or tables with identical primary keys.

To combine multiple tables with the Table Collapsing Wizard:

Select Tools→Denormalization→ Table Collapsing, or right-click a reference between the tables to collapse and select Table Collapsing from the contextual menu, in order to open the Table Collapsing Wizard:

Specify a name and code for the target table to be created, and then click Next to go to the Input Table Selection page.

The Input Table Selection page allows you to select the tables to collapse with the Add Tables tool. Select the check box if you want to keep the original tables after collapsing, and then click Next to go to the Table Collapsing Information page.

The Table Collapsing Information page allows you to specify a name and code for the transformation object that will be created together with the table collapsing. Then click Finish.

The selected tables are collapsed, and a table collapsing object is created.