Building the Analysis Model › Refining the Model › Handling Unusual Situations › Information Hidden in an M:N Relationship

Information Hidden in an M:N Relationship

The injudicious use of M:N relationships can sometimes lead to the loss of important information.

Although M:N relationships sometimes occur naturally, there is often information of interest hidden by the relationship itself. For example, consider the following illustration.

The illustration shows that an order is for one or more products, and that a product appears on one or more orders. While this is true, some important details have been omitted. For example, what is the quantity of each product appearing on a given order? You cannot extract this information from this illustration.

Useful information that is not apparent because of the existence of an M:N relationship is called intersection data.

The need for intersection data calls for the addition of an entity type to hold that data as attributes. By adding an intersection (or associative) entity type and replacing the M:N relationship with two 1:M relationships, you can retain all the information conveyed by the original M:N relationship and also represent additional facts about the intersection.

In the following illustration, the entity type order item has been added to hold the attribute Quantity for a given product on a given order.

As a matter of good practice, carefully evaluate each M:N relationship to ensure that it results in no required information being hidden. It is unusual for a fully detailed business model to retain any M:N relationships.