Self-Referencing 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.]

A self-referencing relationship describes a relationship between two instances in the same extent. While both roles in the relationship are played by instances of the same extent, such relationships are not symmetric – the two roles are normally distinct and not interchangeable. For example, in a mentoring relationship between two people, one role is the mentor, the other the mentee. As a rule, neither role is mandatory because that would result in endless chains of related instances.

Self-referencing relationships highlight the need to handle possible cycles among linked instances. A cycle occurs when a chain of related instances at some point links back to an instance earlier in the chain. Cycles may be valid where different relationships are involved but are normally not valid among instances linked by the same relationship. A direct cyclic reference (a reference from an instance to itself) is sometimes used as a device to terminate a chain of references between different instances. If such direct cyclic references are not permitted, they can be prevented easily using a constraint on the extent. Checking on every update for cycles that may occur across many links can be complex and costly to execute and is usually better done using constraints executed out of band or handled by dedicated functions or by programs written specifically for the task. The self-referencing relationship patterns that follow include constraints that prevent these direct cycles.

One-to-Many Self-Referencing Relationships

A self-referencing, optional one-to-many relationship is commonly used to represent simple, homogeneous, tree-like structures. With an optional relationship, the pattern allows a terminal node that has no parent. The pattern is not intrinsically acyclic or single-rooted but can be constrained to be so if required. The optional nature of the relationship allows the chain to be established or broken at any time.

The self-referencing relationship pattern is a variation of the normal one-to-many pattern, implemented with an optional reference from an extent that targets the same extent. A constraint on the reference can be used to prevent a cyclical reference.

The following code implements a self-referencing mentoring relationship between persons. Note that a separate type and extent are currently required.

module Patterns.Relationships.OneManySelfRef
{
    People : 
    (
        {
            Id : Integer64 => AutoNumber();
            
            Name : Text where value.Count <= 100;
            
            Mentor : People?;
            
        } where 
            identity Id,
            value.Mentor!= value, 
            value.Mentor in People   
    )*;
}

Note that a surrogate identifier is used for the relationship extent rather than a composite identifier based on the two references. In relational implementations, composite primary keys are generally less desirable because they impact the clustering of the index and increase the size of foreign keys and indexes in related tables so should only be introduced after careful consideration of their impact on the overall design.

Many-to-Many Self-Referencing Relationships

A recursive, many-to-many relationship occurs when entities of the same type reference or use one another. Such relationships are normally acyclic and, although the relationship is self-referencing, it is still asymmetric, with discrete roles.

For example, in a friendship relationship between people, each person has a set of friends and may be a member of another person’s set of friends. The asymmetry can be seen in the observation that a person that one individual considers a friend may not be considered a friend by that friend. Mutual friendship is only indicated if there are two relationship instances with the same two persons playing reversed roles in each, such as, where both people assert the other is a friend.

Like the normal many-to-many design pattern, a relationship extent is introduced with a pair of references; only in this case the references both target the same extent. Like the many-to-many pattern, it is good practice to name the extent using a plural noun or noun phrase and to give the references role-based names. If multiple relationship instances are not required between the same pair of instances, then a uniqueness constraint should be introduced across the pair of references. In a simple friendship example, as long as the model needs to only store knowledge of current friendships and does not need to consider tracking on-again,/off-again friendships then this is sufficient. The following code fragment includes this constraint.

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

    Friendship : 
    {        
        Id : Integer64 = AutoNumber();
        
        Friend : People;
                 
        FriendOf : People;

    }* where identity Id,
             unique (Friend, FriendOf),
             value.Friend.Id != value.FriendOf.Id;
}

Note that a surrogate identifier is used for the relationship extent rather than a composite identifier based on the two references. In relational implementations, composite primary keys are generally less desirable because they impact the clustering of the index and increase the size of foreign keys and indexes in related tables so should only be introduced after careful consideration of their impact on the overall design.