Transforms › Use a Transform to Resolve a Many-to-Many Relationship

Use a Transform to Resolve a Many-to-Many Relationship

You can apply a transform and resolve a many-to-many relationship. To avoid design problems that may be caused by many-to-many relationships, database designers frequently add associative entities to act as go-betweens for two ambiguously related entities. If you apply a transform and automatically insert an associative entity between the two entities involved in a many-to-many relationship, you can change the relationship into a series of one-to-many relationships and use the new relationships to clarify how the data model works.

Many-to-Many Relationship Transform Wizard

To resolve a many-to-many relationship with a transform, select the relationship line that connects the entities. Then, click the Many-to-many Transform icon in the Transforms toolbar. Use the Many-to-Many Relationship Transform Wizard to:

• Enter a name and comment for the associative entity.
• Enter a name and definition for the transform.

Result of Resolving a Many-to-Many Relationship

When you click Many-to-many Transform to resolve a many-to-many relationship, you:

• Create an associative entity and connects the associative entity to each source entity with an identifying relationship. The primary key of the associative entity is a combination of the primary key of the source entities. The associative entity appears in the Model Explorer under the Entities folder.
• Add the transform to the Model Explorer under the Transforms folder.

Transforms in the Model Explorer

Each transform that you apply to model objects appears in the Model pane in the Model Explorer. By default, each transform is listed in alphabetical order under the Transforms node.

You can right-click a transform in the Model Explorer and choose Properties to open the Modify Properties for Transform dialog. You use the Modify Properties dialog to change the transform name, add a definition, assign user-defined properties, and specify whether changes made to the target objects are applied to the source objects.

Automatically Resolving Many-to-Many Relationships

To automatically resolve all many-to-many relationships on the logical side of a logical/physical model and create an associative table on the physical side, you can select the Auto apply Many-to-Many transform check box on the General tab of the Model Properties dialog. This check box is only available for logical/physical models.