Many-to-Many Relationships

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

Many-to-many relationships represent usage or reference between instances. The most common is the fully optional many-to-many (*:*) pattern. This is used when an instance of one type can reference zero or more instances of another type, which can be referenced by zero or more instances of the first type. For example, a person can be assigned to zero or more projects and zero or more people can be assigned to a project. People and projects can be created in any order and references between them are created and removed at any time.

Some many-to-many relationships may require that the same pair of instances can be related more than once. For example, if the history of a person’s assignments to projects is to be kept then this may involve multiple assignments of the same person to the same project, perhaps at different times and/or in different roles. It is important to understand if support for multiple references between the same instances is a requirement because it impacts the design.

Design Pattern

The many-to-many design pattern uses an extent to represent the relationship, with two mandatory references, one to each of the related extents. It is good practice to name the relationship extent using the plural form of a noun or noun phrase based on the relationship name, and to name the references using the relationship role names.

The following code illustrates this pattern. This design allows only a single instance of the relationship to exist between the same two entities, which would be appropriate if only the person’s current assignment is to be stored.

module Patterns.Relationships.ManyManyUnique
{
    People :
    {
        Id : Integer64 = AutoNumber();
        
        Name : Text where value.Count <= 100;
        
    }* where identity Id;

    Projects : 
    {
        Id : Integer64 = AutoNumber();
  
        Name : Text where value.Count <= 100;
                     
        Description : Text where value.Count <= 250;
   
    }* where identity Id;

    Assignments : 
    {
        Id : Integer64 = AutoNumber();
  
        AssignedPerson : People;
                     
        AssignedToProject : Projects;        
        
    }* where 
        identity Id,
        unique (AssignedPerson, AssignedToProject);
    
}

A variation that omits the unique constraint over the two references is required if multiple instances of the same relationship can exist between the same two entities. The following code illustrates this and introduces additional fields in an extended Assignments extent that hold start and end dates and a role for the person on the project. With these extensions, a history of multiple assignments to the same project can be stored.

    Assignments : 
    {
        Id : Integer64 = AutoNumber();
  
        AssignedPerson : People;
                     
        AssignedToProject : Projects;        
       
        StartDate : Date;
        
        EndDate : Date?;

        Role : {"Manager", "Contributor"};
        
    }* where identity Id;

Constraints may still be required to prevent duplicated or clashing assignments. For example, the same person might not be allowed to be assigned to the same project with the same role in an overlapping time period.

Do not Use Collections to Model Many-to-Many Relationships

While collections do implement many-to-many semantics, it is not normally recommended to use collections as the normal basis for modeling many-to-many relationships. In many cases, many-to-many relationships require additional information associated with the relationship, such as the date and time of creation added in the preceding example. Using a collection creates an implicit join table, which, as it is not modeled explicitly as an extent, is not apparent in the design and thus cannot be named explicitly or extended with additional fields or references or referenced from other extents. Changing a model later to add an explicit extent is disruptive as it could break application code, so it is recommended that many-to-many relationships are explicitly modeled with relationship extents from the outset.

Many-to-Many Variations

Variations of a many-to-many relationship that require one-or-more semantics (for example, *:+ or +:+) are rare. Enforcing that at least one related instance exists using a hard constraint requires that rows are inserted in two tables simultaneously, which is not allowed without special programming in SQL.