In binary relationships, cardinality constraints are straightforward: we specify how many instances of one entity can associate with instances of another entity. One-to-one, one-to-many, many-to-many—these patterns are intuitive and well-understood.

Ternary relationships introduce a fundamentally different challenge. When three entity types participate simultaneously, we must specify constraints that describe relationships among triples of entities. The question "How many X can associate with each Y?" becomes "How many X can associate with each (Y, Z) pair?" The number of constraint dimensions multiplies, and the interpretation becomes significantly more nuanced.

Many database textbooks gloss over this complexity, leading to widespread confusion among practitioners. This page provides a rigorous, comprehensive treatment of ternary cardinality—equipping you with the theoretical foundation and practical skills to correctly specify and interpret these constraints.

In binary relationships, a cardinality ratio like 1:N means:

"One instance of entity A can relate to many instances of entity B, but each instance of B relates to only one A."

For ternary relationships, we must extend this thinking. The cardinality specification for each participating entity answers a different question:

The Fundamental Question for Ternary Cardinality:

"Given a specific pair of instances from the other two entity types, how many instances of this entity type can participate in the relationship?"

This is the hold-two-constant interpretation: fix instances of two entities and ask how many instances of the third entity can be associated.

Example: SUPPLY(Supplier, Part, Project)

To specify cardinality for SUPPLIER in this relationship, we ask:

"For a given (Part, Project) pair—say (Widget-A, Apollo Project)—how many Suppliers can supply that part to that project?"

• If the answer is "at most one," the cardinality for Supplier is 1
• If the answer is "possibly many," the cardinality for Supplier is M (or N)

Notation Conventions:

Different textbooks and practitioners use varying notations. The most common approaches are:

1. Letter Labels on Lines (Chen-style): Each line from the relationship diamond to an entity is labeled with 1, M, or N indicating that entity's cardinality.

2. Ratio Notation: Expressed as a triple like (1, M, N) indicating cardinalities for the three entities in order.

3. (min, max) Pairs: Each entity is labeled with a (min, max) pair indicating minimum and maximum participation—the most expressive notation.

Regardless of notation, the interpretation is always the same: the number refers to how many instances of that entity can participate when the other two are held constant.

With three participating entities, each having two possible cardinality values (1 or M), we have 2³ = 8 possible cardinality combinations. Each represents a distinct business scenario:

The Mathematical Framework:

Let R(A, B, C) be a ternary relationship. The cardinality ratio (a:b:c) where each value is 1 or M specifies:

• a: For each (B, C) pair, how many A instances can participate
• b: For each (A, C) pair, how many B instances can participate
• c: For each (A, B) pair, how many C instances can participate

Let's examine the most practically significant cardinality patterns with concrete business examples:

Pattern M:M:M — Fully Unrestricted

In SUPPLY(Supplier, Part, Project):

• Any supplier can supply any part to any project
• A specific (supplier, part) combination can supply to multiple projects
• A specific (supplier, project) pair can involve multiple parts
• A specific (part, project) pair can have multiple suppliers

Business Meaning: Complete flexibility—no artificial constraints on the supply arrangements. This is appropriate when the business genuinely has no restrictions.

Pattern 1:1:1 — The Rarest Case

This pattern means:

• For each (B, C) pair, exactly one A
• For each (A, C) pair, exactly one B
• For each (A, B) pair, exactly one C

Mathematical Implication: This is essentially a bijection across all three dimensions. It's rare because it implies extremely tight constraints that most real-world business scenarios don't have.

Example Scenario:

Consider ASSIGNED(Employee, Desk, Shift) where:

• Each (Desk, Shift) has exactly one Employee
• Each (Employee, Shift) has exactly one Desk
• Each (Employee, Desk) occurs in exactly one Shift

This might apply in a strictly controlled work environment where every employee-desk-shift assignment is unique and non-overlapping.

Beyond maximum cardinality (1 or M), ternary relationships can also specify minimum cardinality—whether participation is mandatory or optional. The (min, max) notation provides this expressiveness.

Total vs. Partial Participation in Ternary:

For binary relationships:

• Total participation: Every entity instance must participate in at least one relationship instance
• Partial participation: Some entity instances may not participate

For ternary relationships, this extends to:

"Must every instance of entity A participate in at least one triple in the relationship?"

Example Analysis: PRESCRIBES(Doctor, Medication, Patient)

• Doctor participation: Must every doctor have at least one prescription? Typically partial—newly hired doctors or specialists might not prescribe.
• Medication participation: Must every medication be prescribed at least once? Partial—medications may exist in the formulary without current prescriptions.
• Patient participation: Must every patient have at least one prescription? Partial—healthy patients or those on non-pharmaceutical treatments might have no prescriptions.

A powerful alternative way to understand and specify ternary cardinality is through functional dependencies (FDs). This approach is particularly valuable because FDs translate directly into relational design constraints.

The Connection Between Cardinality and FDs:

For a ternary relationship R(A, B, C):

• If the cardinality for A is 1 (meaning for each (B, C) pair there's at most one A), then:

  B, C → A
  

  The combination of B and C functionally determines A.

• If the cardinality for A is M, there is no such functional dependency.

Complete FD Analysis for All Patterns:

Practical Application:

When designing the relational schema for SUPPLY(Supplier, Part, Project) with pattern 1:M:M:

• FD: Part_ID, Project_ID → Supplier_ID
• This means the primary key can be (Part_ID, Project_ID)
• Supplier_ID is a non-key attribute determined by the composite key

This is more efficient than a three-attribute key because:

1. Indexes are smaller
2. Uniqueness is easier to verify
3. Foreign key references are simpler

Conversely, for M:M:M with no FDs, the primary key must be (Supplier_ID, Part_ID, Project_ID)—all three attributes.

Converting business rules to cardinality specifications requires a systematic approach. Here's a step-by-step methodology:

Step 1: Identify the Ternary Relationship

Clearly name the relationship and its three participating entities:

ADVISES(Professor, Student, Project)

Step 2: Apply the Hold-Two-Constant Test for Each Entity

For each of the three entities, ask the constraining question:

Ternary cardinality is frequently misunderstood. Here are the most common errors and how to avoid them:

Cardinality in ternary relationships is more complex than in binary relationships, but the underlying principle is consistent: we specify constraints on participation by fixing context and counting possibilities.

What's Next:

Now that you understand how to specify and interpret cardinality in ternary relationships, the next page addresses a critical design question: When should you use a ternary relationship? We'll explore the decision criteria, considering when ternary relationships are necessary versus when decomposition into binary relationships is preferable.