Mapping One-to-Many and One-to-One Relationships

Learning outcomes

Relational databases implement binary one-to-many and one-to-one relationships primarily through foreign keys, nullability, and uniqueness. The difficult part is not syntax; it is placing the foreign key on the correct side and preserving the minimum and maximum participation rules established by the conceptual model.

The 1:N mapping rule

For a one-to-many relationship, copy the primary key of the one-side entity into the many-side relation as a foreign key.

WorkshopHub:

Customer 1 -------- 0..* Asset

maps to:

CREATE TABLE asset (    asset_id     INTEGER PRIMARY KEY,    customer_id  INTEGER NOT NULL,    model_name   TEXT,    FOREIGN KEY (customer_id)      REFERENCES customer(customer_id));

Many Asset rows may reference the same Customer row.

Why the foreign key belongs on the many side

If Customer stored one asset_id, it could reference only one asset. Numbered columns such as asset1_id, asset2_id, and asset3_id would impose arbitrary limits. A foreign key on Asset naturally represents any number of assets per customer.

Mandatory versus optional child-to-parent participation

If every Asset must have one current Customer owner:

customer_id INTEGER NOT NULL

If unowned assets are valid:

customer_id INTEGER NULL

Do not make the column nullable merely because application code wants to create incomplete objects. Nullability should reflect valid business states.

One-to-one needs uniqueness

A simple foreign key implements many-to-one, not one-to-one. Suppose an Employee may have at most one PrivateProfile:

CREATE TABLE employee_private_profile (    private_profile_id INTEGER PRIMARY KEY,    employee_id        INTEGER NOT NULL UNIQUE,    government_id      TEXT,    FOREIGN KEY (employee_id)      REFERENCES employee(employee_id));

UNIQUE(employee_id) ensures no two profiles reference the same employee.

Shared primary-key one-to-one

When the child cannot exist without the parent and one-to-one identity is strong, use the parent's key as the child's primary key:

CREATE TABLE employee_private_profile (    employee_id   INTEGER PRIMARY KEY,    government_id TEXT,    FOREIGN KEY (employee_id)      REFERENCES employee(employee_id));

This says one profile per employee and gives the profile no separate surrogate identity.

Which side should hold a 1:1 foreign key?

Consider optionality and lifecycle. If every Passport belongs to one Person but not every Person has a Passport, storing person_id on Passport is natural. If two optional extensions exist around a core entity, shared primary-key child tables can preserve clean separation.

Questions to ask:

• Which entity can exist independently?
• Which side is optional?
• Which side is created later?
• Which side contains sensitive data that deserves physical separation?

Relationship attributes in a 1:N mapping

If the relationship itself has attributes, a simple foreign key may be insufficient. Suppose Asset's current Customer relationship needs ownership_started_at. You could store that column on Asset if only current ownership matters:

asset.customer_idasset.ownership_started_at

But if ownership history matters, the relationship should become a separate Ownership entity with start/end dates.

Current-state foreign key versus historical association

Overwriting asset.customer_id destroys prior ownership unless history is captured elsewhere. A historical model could be:

AssetOwnership(  asset_ownership_id,  asset_id,  customer_id,  valid_from,  valid_to)

Then current ownership is derived from the open interval or a designated current row.

Referential actions are lifecycle decisions

Do not use cascade mechanically

Suppose Customer is deleted. Cascading through Asset to WorkOrder could erase years of service history. If retention is required, Customer should probably be deactivated or anonymized rather than physically removed.

Multiple relationships between the same tables

WorkOrder may reference Employee in different roles:

opened_by_employee_idapproved_by_employee_idclosed_by_employee_id

Each foreign key represents a different relationship. Role-specific names preserve semantics.

Self-referencing one-to-many

Technician mentorship:

CREATE TABLE technician (    technician_id        INTEGER PRIMARY KEY,    full_name            TEXT NOT NULL,    mentor_technician_id INTEGER,    FOREIGN KEY (mentor_technician_id)      REFERENCES technician(technician_id));

One mentor can have many mentees; each mentee has zero or one mentor.

WorkshopHub mapping

CREATE TABLE work_order (    work_order_id INTEGER PRIMARY KEY,    asset_id      INTEGER NOT NULL,    status_code   TEXT NOT NULL,    opened_at     TEXT NOT NULL,    closed_at     TEXT,    FOREIGN KEY (asset_id)      REFERENCES asset(asset_id)      ON DELETE RESTRICT,    FOREIGN KEY (status_code)      REFERENCES work_order_status(status_code));

This maps Asset 1:N WorkOrder and WorkOrderStatus 1:N WorkOrder.

One-to-one mapping test

Summary and next lesson

1:N relationships place the foreign key on the many side; 1:1 relationships require uniqueness or a shared key. Nullability represents optional participation only when the business permits absence, and referential actions must follow lifecycle semantics. The next lesson maps many-to-many relationships through associative relations.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• C. J. Date, Database Design and Relational Theory.
• PostgreSQL and SQLite documentation for foreign-key constraints.