One-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.]

One-to-many relationships are the most common relationship pattern. Of these, the most common are mandatory one-to-many relationships (1:*). For example, in a simple contact model that involves persons and addresses, a person may be allowed zero or more addresses; while each address is associated with exactly one person. Mandatory one-to-many relationships normally occur between instances of different types. Mandatory self-referencing relationships (between instances of the same type) rarely occur because they result in endless chains.

Design Pattern

With a relational implementation, a one-to-many relationship is normally mapped to a singleton reference from the extent that implements the ‘many’ role in the relationship to the extent that implements the ‘one’ role. The reference results in a column constrained by a foreign key constraint in the corresponding table.

It is good practice to give the reference a meaningful name based on the role the referenced instance plays in the relationship. The following code is for the Person and Address example.

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

Variations

Less common variations of the basic one-to-many pattern are optional one-to-many (?:*); optional one to one-or-more (?:+); and one to one-or-more (1:+).

An optional one-to-many relationship (?:*) is used when the related entities can exist independently. For example, if the simple contact model is extended to allow Addresses to exist independently of People. The corresponding design pattern uses an optional Person reference to the People extent in Addresses. At an instance level, this reference can be added or nullified independently of the creation and deletion of an address. This pattern is illustrated in the following code.

module Patterns.Relationships.OneManyOptional
{
    People :
    {
        Id : Integer64 = AutoNumber();
        
        Name : Text where value.Count <= 100;
        
    }* where identity Id;
    
    Addresses : 
    {
        Id : Integer64 = AutoNumber();
        
        Person : People?;
                     
        AddressText : Text where value.Count <= 250;
   
    }* where identity Id;
    
} 

Do Not Use Collections to Model One-to-Many Relationships

At first glance, collection reference fields appear to be a natural way to implement one-to-many relationships. However, the elements in the collection are referenced and are implemented by a join table in SQL. Thus collections, in fact, implement many-to-many not one-to-many semantics.

For example, the following ContactPoints collection in the sample results in a People_ContactPointsTable join table that allows any number of people to reference the same address as a contact point.

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

        ContactPoints : Addresses*;
        
    }* where identity Id;
    
    Addresses : 
    {
        Id : Integer64 = AutoNumber();
                     
        AddressText : Text where value.Count <= 250;
   
    }* where identity Id;

}