How do you map a three-way (ternary) relationship — for example which supplier supplies which part to which project, with a quantity — into relational tables, and what should that table's key be?
answer
- one table, three FKs, plus attributes
- default PK = all three keys
- cardinality can shrink the key
- can the triple repeat over time?
- pairwise decomposition invents triples
basics
~20 sIt becomes one table holding all three participants' primary keys as foreign keys, plus the relationship's own attributes. The default primary key is all three key columns together; cardinality constraints can narrow it to a subset, and repetition over time adds a component.
solid answer
~50 sA ternary relationship maps to a single table containing the primary key of each of the three participants as a foreign key, plus any relationship attributes such as quantity. The default primary key is the combination of all three foreign keys, asserting that each supplier-part-project triple is recorded at most once. Cardinality can narrow it: if for a given part and project there can be only one supplier, then (part, project) is already unique and is the better key, with supplier as a plain non-null column. The important check is whether the triple can repeat legitimately, most often because the fact is time-varying — the same supplier supplying the same part to the same project in different periods. Then the key needs a period or sequence component, and the all-three key would silently reject valid data. Three separate pairwise tables are not equivalent: joining them produces combinations that were never recorded, and the quantity has no pair to attach to.
code
sql · 7 linesCREATE TABLE supply (
supplier_id BIGINT NOT NULL REFERENCES supplier(supplier_id),
part_id BIGINT NOT NULL REFERENCES part(part_id),
project_id BIGINT NOT NULL REFERENCES project(project_id),
quantity INT NOT NULL,
PRIMARY KEY (supplier_id, part_id, project_id)
);go deeper
Knowing that a three-way relationship becomes one table holding all three keys is sufficient at this level.
Add the default all-three primary key and explain why no participant's row can hold the association.
Derive the key from the ternary cardinality, check for legitimate repetition over time, and show why pairwise decomposition invents facts.
Decide between a keyed relationship table and a reified entity with a surrogate identifier, and place the constraints that keys cannot express.
## The mapping rule A relationship of degree three maps to a table of its own containing the primary key of each participating entity, each declared as a foreign key, plus the relationship's attributes. One row is one recorded three-way fact. There is no alternative structure. Unlike one-to-many, no participant's row can hold the association, because a single row on any one side may take part in many triples and no column can encode a triple. ## Choosing the key The general default is the composite of all three foreign keys, which says each triple appears once. Cardinality can shrink it. Ternary cardinality is stated per participant given the other two: "for a given part and project, how many suppliers?" If the answer is at most one, then (part_id, project_id) already determines the row, and that pair is the correct primary key with supplier_id as an ordinary non-null column. Using the wider all-three key in that case would permit two suppliers for the same part and project, contradicting the stated rule. Working this out is the substance of the question; reciting "all three columns" without checking is the shallow answer. ## The repetition and time check Before committing to any of those keys, ask whether the same triple can legitimately occur more than once. The usual reason is time: the same supplier supplied the same part to the same project last quarter and again this quarter, at a different quantity. If so, the key needs a period or sequence component, and the relationship is arguably about a supply event rather than a static association. This is the same question asked of junction tables generally, and it is the most common reason a mapped schema has to be changed after the fact. ## Why three binary tables are not a substitute Suppose you store supplier-part, part-project and supplier-project separately. Joining them reconstructs more triples than were ever true: if Acme supplies bolts, bolts go to the Bridge project, and Acme also works with the Tunnel project, the join suggests Acme supplies bolts to the Tunnel project. The relationship's attribute makes it starker — a quantity is a quantity per triple and has no pair to live on. Any candidate who offers the decomposition should be asked where the quantity goes; the question answers itself. The converse also matters: if the fact genuinely does decompose — the three pairwise facts are independent and reconstruct exactly — then it was never a ternary relationship, and three tables are the right answer. The mapping follows the model; the modelling decision comes first. ## Constraints that will not fit Ternary cardinality rules frequently exceed what keys and unique constraints can state, especially minimums such as "every project must have at least one supplier for each of its required parts". Those become application invariants or scheduled checks. Naming that gap is part of a complete answer, because the mechanical mapping does not warn you about it. ## Promote it when it has a life If the triple carries a status, an approver, effective dates, or is referenced from elsewhere, it is a Supply Agreement or a Delivery rather than a relationship. Give it a surrogate identifier so other tables can point at it, keep a unique constraint over whichever combination expresses the real rule, and let it acquire its own attributes. The structural change is small; the modelling clarity is large, and it avoids other tables having to carry three foreign key columns just to reference one fact. ## Practical shape In practice a supply table with (supplier_id, part_id, project_id) and quantity, keyed per the cardinality analysis and indexed for the access paths that matter, is the whole answer. Ternary relationships are uncommon enough that the interviewer is mainly checking that you do not reflexively decompose them and that you can reason about the key rather than reciting one.
- When is the primary key of a ternary table narrower than all three foreign keys?When the cardinality says one participant is determined by the other two — for a given part and project there is at most one supplier. Then (part_id, project_id) is the correct key and supplier_id is an ordinary non-null column. Keeping all three columns as the key in that case would allow two suppliers for the same part and project, permitting data the rule forbids.
- Why can a ternary relationship not be represented by a foreign key on one of the participants?Because a row on any one side can take part in many triples, and a column holds one value. Even where cardinality restricts one participant, the row still needs both of the other two keys to identify which triple it belongs to, which is more than a single foreign key column can express. A dedicated table is the only faithful structure.
saying these in an interview costs you the question
- Splitting a genuinely three-way fact into three pairwise tables and losing valid combinations
- Reciting all-three-columns as the key without checking the cardinality constraints
- Failing to ask whether the triple can repeat, so a time-varying fact cannot be recorded
- Attaching the relationship's quantity to one of the participating entities
- Assuming every ternary cardinality rule can be expressed with keys and unique constraints