ER/Studio Denormalization Feature Example

By: Gregory Keller

Abstract: A brief overview of using ERStudio's Roll-Up Denormalization Feature

ER/Studio 6.5 and greater (the current GA is 6.6.1) actually *does* offer pretty sophisticated schemes to resolve your supertype/subtype structures when generated to the physical model. These resolutions, referred to as “Denormalizations”, are not found in the Generate Physical Model Wizard (nor the logical model itself) for a very specific purpose: we consider them physical model ‘design time’ decisions best suited for when the model is generated so a user isn’t forced ‘up front’ to denormalized structures…potentially 100’s of structures on large models.

When you generate a physical model, you will find a series of Denormalization options (all wizard driven) to help you….or someone else collaborating on the physical portion of the diagram…‘resolve’ various design aspects to better improve how they should exist on a physical database (super type/subtype resolutions being the most common).

The Denormalization options ERStudio offers in the physical model are:

  • Column Mapping
  • Roll Ups
  • Roll Downs
  • Horizontal Table Splits
  • Vertical Table Splits
  • Table Merges

Here’s an example:

When you generated your physical model fro the logical, the Treaty Supertype and its Subtypes generally were generally created 1 table for 1 entity…like this:

Now, assume you wanted Retro_Treaty and Ceded_Treaty to be ‘rolled up’ into Treaty. You would do the following:

  1. Select the 3 objects above and the Roll Up or Roll Down icons become enabled:

Hide image

  1. Launch the Roll Up wizard and walk through it to completion. The resultant Treaty table now looks like this:

  1. Now, with this action done, let’s assume you’d like to “look down” from the logical “Retro Treaty” entity and see what it’s physical instantiation is. IN the Entity Editor, go to “Where Used”:

Hide image
Click to see full-sized image

  1. Notice that we indicate that Retro_Treaty now is Treaty…and why? Well, we give you the whole audit trail (the Denormalization mapping) to ensure you know why Retro_Treaty now = Treaty.

There are specific “rules of engagement’ for when each of these denormalization mappings can occur. Example: You can never Roll Up two tables joined by an non identifying relationship (as there is NO way ERStudio…or a data modeler in many cases!...would know how to uniquely identify the data in either the parent or child tables. Thus, only pairs of tables joined by identifying relationships can be rolled up or rolled down (as would be the case of Supertype/Subtype resolutions).

All of these concepts can be learned about here in our Help system:

Hide image
Click to see full-sized image

Server Response from: ETNASC03