One-to-One, One-to-Many, and Many-to-Many Cardinality

Learning outcomes

Cardinality states how many instances of one entity type may or must be associated with instances of another. Getting cardinality wrong is one of the fastest ways to make a schema unable to represent valid business cases. This lesson separates one-to-one, one-to-many, and many-to-many structure from actual current row counts.

Cardinality is a rule, not a snapshot

Suppose the database currently contains one work order per asset. That does not prove Asset–WorkOrder is one-to-one. If the business allows the same asset to return for service many times, the relationship is one-to-many even when today's sample data happens to contain one row each.

One-to-many

WorkshopHub's Asset–WorkOrder relationship is naturally one-to-many:

Asset 1 --------< WorkOrderOne Asset may have many WorkOrders.Every WorkOrder belongs to one Asset.

In a relational schema, the foreign key typically appears on the many side:

CREATE TABLE work_order (    work_order_id INTEGER PRIMARY KEY,    asset_id      INTEGER NOT NULL,    ...    FOREIGN KEY (asset_id) REFERENCES asset(asset_id));

The foreign key allows many WorkOrder rows to reference the same Asset.

How to identify the many side

Ask two directional questions:

1. For one Asset, how many WorkOrders can exist over time?
2. For one WorkOrder, how many Assets can it service?

If the answers are “many” and “one,” you have one-to-many. This two-direction technique avoids wording traps.

One-to-one

A one-to-one relationship means each instance on either side is related to at most one instance on the other side. Real one-to-one relationships exist, but beginners often overuse them.

Possible examples include:

• Person and one optional biometric profile, if the system guarantees at most one profile per person;
• Employee and one separately stored payroll-secret record, when separation exists for security or operational reasons;
• Vehicle and one current registration record in a narrowly defined jurisdiction/model.

Often a supposed one-to-one pair could simply be one entity split across tables for physical, security, or lifecycle reasons. Conceptually, ask whether there really are two independently meaningful entity types.

Implementing one-to-one

A foreign key alone creates many-to-one. To enforce one-to-one, the foreign key must also be unique:

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

Using employee_id as both primary key and foreign key guarantees at most one private profile per employee.

Many-to-many

Technician–WorkOrder is many-to-many if:

• one technician can work on many orders;
• one work order can have many technicians.

Technician >-----< WorkOrder

Relational databases normally implement this through an associative relation such as WorkOrderAssignment. Lesson 5 develops that transformation carefully.

Hidden many-to-many relationships

Many-to-many relationships are often hidden when requirements say “has” without specifying counts. Examples:

• Students take Courses.
• Doctors treat Patients.
• Products appear on Orders.
• Users belong to Organizations.
• Articles have Tags.

Each must be tested in both directions. One Article can have many Tags; one Tag can label many Articles. Therefore the structure is many-to-many.

Cardinality can change with scope

“An employee belongs to one department” may be true if the model stores only the employee's primary department. It may be false if matrix organizations allow concurrent departmental assignments. Similarly, “an asset has one owner” depends on whether co-ownership and ownership history are in scope.

Cardinality is therefore not an objective property of the real world detached from requirements. It is a rule within the model's defined scope and time semantics.

Current state versus history

Time often turns an apparent one-to-one relationship into one-to-many. For example:

• Asset has one current owner, but many ownership periods over history.
• Employee has one current manager, but many manager assignments over a career.
• WorkOrder has one current status, but many status-change events.

If history matters, model the time-bounded relationship or event rather than overwriting the current foreign key.

Cardinality and business uniqueness

Cardinality is implemented through combinations of foreign keys and uniqueness constraints.

Do not encode “many” with repeating columns

This design does not model one work order with many technicians correctly:

WorkOrder(  technician1_id,  technician2_id,  technician3_id)

It imposes an arbitrary maximum, repeats the same concept across columns, complicates querying, and makes relationship attributes difficult. “Many” should normally mean multiple related rows, not numbered columns.

Do not encode many-to-many with comma-separated values

technician_ids = '17,23,44'

This defeats foreign-key integrity and makes joins, updates, indexing, and constraint enforcement difficult. Each relationship instance should be representable as structured data.

WorkshopHub cardinality review

Practice: discover the cardinality

Summary and next lesson

Cardinality expresses how many relationship instances are valid over the model's scope and lifetime. One-to-one, one-to-many, and many-to-many are rules, not observations about current row counts. The next lesson adds the minimum side of the constraint: whether participation is mandatory or optional.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Peter P. Chen, “The Entity-Relationship Model—Toward a Unified View of Data,” 1976.
• Thomas Connolly and Carolyn Begg, Database Systems.