Designing the Data Structure › 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, as described in the Associative Entity Type section. 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.

The following illustration shows a many-to-many relationship:

This illustration is a data model fragment showing the following relationships:

• 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 illustration is a Data Structure List showing the implementation of the M:M relationship.

Type            Macro Name            Format     Length Optional
                                                                   
Table             EMPLOYEE                                         
Column            NUMBER                Integer      4 	Not Null   
Column            NAME                  	Char        30 	Not Null   
Column            ADDRESS               	Char        78 	Not Null   
Index (U)         PKEY (Primary)                                   
Column            NUMBER                	Integer      4 	Not Null   
                                                                   
Table             IS_POSSESSED_BY                                  
FK Column         FK_SKILL_TYPE         	Char        15 	Not Null   
FK Column         FK_EMPLOYEE_NUMBER    	Integer      4 	Null       
RI Contrating     <No Name> EMPLOYEE                               
RI Constraint     <No Name> SKILL                                  
Index (U)         FKEY                                             
Column            FK_EMPLOYEE_NUMBER     Integer      4 	Not Null   
Column            FK_SKILL_TYPE          Char        15 	Not Null   
                                                                   
Index (U)         PKEY (Primary                                    
Column            FK_SKILL_TYPE          Char        15 	Not Null   
Column            FK_EMPLOYMENT_NUMBER   Integer      4 	Not Null   
                                                                   
Table             SKILL                  Char        15 	Not Null   
Column            TYPE                   Char        78 	Not Null   
Column            DESCRIPTION                                      
Index (U)         PKEY (Primary)         Char        15 	Not Null   
Column            TYPE  

Note: 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 way, 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.