How to Identify One-to-One Relationships in Database Design

A one-to-one (1:1) relationship permits at most one related instance in each direction. To identify it, ask two questions: how many B instances may belong to one A, and how many A instances may belong to one B? Both maximums must be one. Whether either instance may exist without the other is a separate question about participation.

This lesson develops the Person and current Passport example from Lesson 4. It explains how to test the business rule, recognize misleading examples, and select constraints that enforce the intended relationship.

Begin with what the application must allow, rather than the rows currently stored. A table containing one related row today may still permit several tomorrow.

1. Define what one row represents. Does Passport represent a current passport record or every passport issued over a person's lifetime?
2. State the maximum in each direction. In the example system, one Person may have at most one current Passport record; one Passport belongs to exactly one Person.
3. State the minimum separately. A Person may have no Passport record. A stored Passport must reference a Person.
4. Confirm the rule with its owner. If multiple current passports must be supported, the example's one-to-one rule no longer describes the requirement.
5. Test a boundary case. Ask whether a second Passport row for the same PersonID should be accepted or rejected, and why.

Review mandatory and optional participation for the distinction between a minimum of zero and a minimum of one.

• Maximums: At most one related row is permitted in each direction.
• Minimums: Specify whether each entity may exist without its counterpart.
• Scope: Distinguish a current record, a selected record, and a history of records.
• Enforcement: Identify which key and constraints enforce each part of the rule.
• Purpose: Explain why the information belongs in two tables rather than one.

A filtered or partial unique index can enforce some selected-row rules in DBMSs that support it. For example, it can limit a customer to at most one address marked primary. That does not require a primary address to exist. Historical date ranges may also need overlap checks; uniqueness of a current flag alone does not prevent overlapping past appointments.

Place the reference according to ownership and participation. In the Person/Passport example, Passport is the dependent entity: a passport record cannot exist without its person, while a person can exist without a passport record.

• Foreign key: Passport.PersonID must match an existing Person.PersonID.
• NOT NULL: Every stored passport must supply that reference.
• UNIQUE: Two passport rows cannot reference the same person.

These constraints allow a Person row with no matching Passport row. Making the foreign key non-null does not make participation mandatory on the Person side. If both entities must always have a counterpart, an additional enforcement mechanism appropriate to the DBMS is needed, or the facts may belong in one table. Creating both rows in a transaction is useful, but it is not by itself a database-wide guarantee that every writer will obey the rule.

If either entity may exist independently, a nullable unique foreign key is one possible design. Check the DBMS's treatment of nulls in unique constraints; it varies between implementations. Choose the referencing side from the business rule rather than assuming the smaller table is always the correct choice.

For supporting concepts, review data integrity elements and constraint types.

A unique foreign key gives each table its own identifier. A Passport has a PassportID and a unique PersonID reference. This is the pattern used in the worked example below.

A shared primary key makes the dependent table's PersonID both its primary key and its foreign key. Because a primary key is unique and non-null, no second dependent row can use the same PersonID.

-- Alternative to the Passport definition below.
-- Create Person first; use only one Passport design.
CREATE TABLE Passport (
  PersonID INT NOT NULL PRIMARY KEY,
  PassportNumber VARCHAR(30) NOT NULL,
  CONSTRAINT fk_passport_person
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);

Both patterns enforce the same upper bound. Neither requires every Person to have a Passport. Decide deletion and retention behavior separately: removing a person does not automatically imply that a dependent record should be deleted. Use cascading deletion only when the business policy calls for it.

The system stores at most one current passport record per person. A person may have none; every passport record belongs to one person. This is the same simplified rule used in Lessons 3 and 4, rather than a universal rule about passports or citizenship. Supply identifiers explicitly in these examples.

CREATE TABLE Person (
  PersonID INT NOT NULL PRIMARY KEY,
  PersonName VARCHAR(100) NOT NULL
);

CREATE TABLE Passport (
  PassportID INT NOT NULL PRIMARY KEY,
  PersonID INT NOT NULL,
  PassportNumber VARCHAR(30) NOT NULL,
  CONSTRAINT uq_passport_person UNIQUE (PersonID),
  CONSTRAINT fk_passport_person
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);

In the unique-foreign-key design, join the tables using Passport.PersonID = Person.PersonID. PassportID identifies a passport row; it does not have to equal the person's identifier. If all historical passports must be retained, revise the data model instead of using this uniqueness rule on the entire history.

A one-to-one association can reflect a distinct dependent entity or a deliberate division of information about the same entity. Separate tables may be useful when information has different access permissions, is rarely present, or follows a different retention policy. Permissions still need to be configured; placing sensitive columns in a second table does not protect them automatically.

Keep attributes together when they describe the same entity, are usually used together, and share the same access and retention rules. An additional table adds joins and maintenance work. Any performance benefit from splitting rows depends on the workload, indexes, and DBMS.

“Employees are assigned their own office” is incomplete until “own” is defined. A one-to-one rule would say: each employee may occupy at most one office, and each office may be occupied by at most one employee. Specify whether unassigned employees and empty offices are allowed.

If an office can be shared, the office-to-employee maximum becomes many. If an employee can occupy several offices and offices can be shared, the association becomes M:N. Discuss possible policy changes with stakeholders, but implement the confirmed rule rather than silently allowing sharing that the organization currently forbids.

1. Does one related row in today's data prove 1:1? No. The business rule must limit both directions to at most one.
2. What changes if Passport.PersonID loses its unique constraint? Several passport rows can reference one person, permitting a 1:N relationship.
3. Does a non-null Passport.PersonID require Ben to have a passport? No. It applies to Passport rows that exist, not to the existence of a Passport row for each Person.

Complete the one-to-one relationships exercise to practice interpreting business rules.