One-to-One 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.]
The most commonly occurring one-to-one relationship is an optional one-to-one (1:?) relationship. This pattern is most frequently encountered when a complex entity is partitioned into a primary extent and one or more subordinate or secondary extents that together store all the information about the entity. This pattern occurs when some significant portion of the information about a complex entity is optional and often null, or has different access or security profiles and so warrants storing in a separate extent. This design requires an optional one-to-one relationship between the primary extent and each subordinate extent.
For example, information about a person might be partitioned across a primary People extent, and subordinate extents, PersonalInformation and EducationalInformation. Collectively, the three extents hold all the information stored about a person. An instance in the People extent can exist without a corresponding instance in PersonalInformation or EducationalInformation. Conversely, an instance in either PersonalInformation or EducationalInformation must be associated with an instance in the People extent.
Design Pattern
An optional one-to-one (1:?) relationship is a restriction over a one-to-many (1:*) relationship, so a constrained variation of the one-to-many design pattern is used. A mandatory reference from the secondary extent to the primary extent is used, with an additional constraint on the reference to ensure it is unique to enforce one-to-one rather than one-to-many semantics. This can be done by making the reference to the primary extent the identifier of the secondary extent. Where this isn’t desired or practical, the same effect can be achieved by adding a uniqueness constraint over the reference.
The two variations are illustrated in the following code. PersonalInformation is identified by its reference to People, while EducationalInformation has separate identifier and reference fields, with the reference to People constrained by a uniqueness constraint.
module Pattern.Relationships.OneOneOptional
{
People :
{
Id : Integer64 = AutoNumber();
Name : Text where value.Count <= 100;
}* where identity Id;
PersonalInformation :
{
Person : People;
BirthDate : Date?;
Hobbies : Text?;
}* where identity Person;
EducationalInformation :
{
Id : Integer64 = AutoNumber();
Person : People;
University : Text where value.Count <= 100;
HighestQualification : Text where value.Count <= 100;
}* where
identity Id,
unique Person;
}
Both of these variations allow additional secondary extents to be added to the model without changing the primary extent or data stored in it. If a separate identifier and reference are used, it is also easy to extend the relationship from an optional one-to one to a one-to-many relationship by removing the uniqueness constraint on the reference.
Do Not Implement an Optional One-to-One Relationship using an Optional Reference
It is possible to implement an optional one-to-one relationship with an optional reference from the primary to the secondary extent. This design pattern, like the reference collection example discussed earlier, may seem at first to be a natural implementation, particularly as it has an object-oriented “feel” to it. However, this is much less natural in a relational implementation where a uniqueness constraint cannot normally be used with a nullable field. A null value is treated like any other value so only one null value is allowed for each extent. Without such a constraint the reference implements many-to-one semantics from the primary to the secondary extent. While a constraint could be added manually as a check function on the table, it would be less efficient than using a unique index-based check. Using such optional references also results in a less extensible schema – adding additional secondary extents requires the deployed primary extent to be modified to add the references, while extending a relationship from one-to-one to one-to-many requires the reference to be reversed or the reference removed and a relationship extent added.
One-to-One Relationship Variations
Fully-optional one-to-one (?:?) relationships are uncommon in models. This pattern is used to correlate instances in different parts of a model or in different models, where instances on both sides of the relationship can exist without the other. Implementing optional references from one or both extents has all the problems discussed previously and is not recommended. If a fully-optional relationship is required, a dedicated relationship extent should be used with additional constraints to enforce the reduced multiplicity – effectively a constrained variation of the many-to-many pattern.
The following code uses this pattern to correlate instances in the Employees extent with instances in the People extent. Note the uniqueness constraints used to enforce the one-to-one semantics of the relationship.
module Patterns.Relationships.OneOneFullyOptional
{
People :
{
Id : Integer64 = AutoNumber();
Name : Text where value.Count <= 100;
}* where identity Id;
Employees :
{
Id : Integer64 = AutoNumber();
FirstName : Text where value.Count <= 50;
LastName : Text where value.Count <= 50;
EmployeeNumber : Text where value.Count <= 25;
}* where identity Id;
PersonEmployeeCorrelation :
{
Id : Integer64 = AutoNumber();
Person : People;
Employee : Employees;
}* where
identity Id,
unique Person,
unique Employee;
}
True fully-mandatory one-to-one (1:1) relationships are rare and best avoided if possible. If implemented with two references, it would require that two interdependent inserts occur simultaneously, which is not allowed without special programming in T-SQL. In most cases, the desired effect can be achieved with an optional one-to-one (1:?) relationship and a separate check that enforces the existence of both instances invoked out of band.