Designing the Data Structure › Using the Data Structure List › Many-to-Many Relationships

Many-to-Many Relationships

Many-to-many (M:M) relationships should have been resolved in analysis by defining an additional entity type.

Most M:M relationships have some extra information that needs to be kept on an associative entity type (see Associative Entity Type). Even if you do not find this during the initial implementation of an application, it will often happen later.

Resolving the M:M into an associative entity type results in a more stable model and requires fewer action diagramming changes in additional intersection data is found after the initial version of an application has been implemented. If this has not been done, any remaining M:M relationships are implemented with another table.

A many-to-many relationship is illustrated in the following Illustration:

This figure is a data model fragment showing:

• Each EMPLOYEE possesses one or more SKILLS
• Each SKILL is possessed by one or more EMPLOYEES.

The identifier of EMPLOYEE is the attribute Number.

The identifier of SKILL is the attribute Type.

The following figure is a Data Structure List showing the implementation of the figure “M:M Relationship.”

Note that a table joins the EMPLOYEE and SKILL tables and its name is “IS POSSESSED BY” (the name of one of the relationship memberships). The link record contains both the key of the EMPLOYEE table and the key of the skill table. This means that if the database is queried by EMPLOYEE number, it can find all the SKILLS that the EMPLOYEE possesses, and given a SKILLTYPE, it can find all the EMPLOYEES who possess it.