Where do attributes that describe a relationship itself belong — for example a student's grade in a course, or the date a user joined a team — and how does the model change when that relationship has to keep history?
answer
- Determined by both keys → attribute of the link
- Repeating columns per parent = misplaced link attribute
- History changes identity, not just columns
- valid_from/valid_to + one-open-row rule
- Delete becomes close; every query needs an as-of predicate
basics
~20 sThey belong on the junction row, because they depend on both sides and on neither alone. Once the same pair can recur over time, add validity dates to the key or a surrogate key, and the link becomes an entity with its own lifecycle.
solid answer
~1 minAn attribute belongs to the relationship when it is functionally determined by **both** keys together. A grade is not a property of the student and not a property of the course — it exists only for the pair, so it lives on the enrolment row. Same for `joined_at` on a membership, `quantity` and `unit_price` on an order line, `role` on a project assignment. Pushing such a column onto either parent forces repeating columns or loses information as soon as there is more than one pair. History changes the identity of the row. As long as a pair can exist at most once, `PRIMARY KEY (a_id, b_id)` is right. Once someone can leave a team and rejoin, or re-take a course, that key forbids the truth. Two workable shapes: - **Temporal link:** add `valid_from` / `valid_to`, key on `(a_id, b_id, valid_from)`, and enforce non-overlap — an exclusion constraint where available, otherwise a uniqueness rule on "currently open" rows. - **Event log plus current state:** append immutable join/leave events and maintain a current-membership table for reads. At that point the junction is a full associative entity: it may need its own surrogate key, its own audit columns, and other tables may reference it.
code
sql · 13 linesCREATE TABLE membership (
user_id BIGINT NOT NULL REFERENCES app_user(id),
group_id BIGINT NOT NULL REFERENCES app_group(id),
role TEXT NOT NULL,
valid_from TIMESTAMPTZ NOT NULL,
valid_to TIMESTAMPTZ,
PRIMARY KEY (user_id, group_id, valid_from)
);
-- at most one open (current) membership per pair
CREATE UNIQUE INDEX membership_current_uq
ON membership (user_id, group_id)
WHERE valid_to IS NULL;go deeper
Say that attributes describing the pairing go on the junction row, and give an example such as a grade or a quantity.
Justify it with the dependency test — the value is determined by both keys — and note that the composite key assumes the pair happens at most once.
Show that history changes identity: extend the key with a validity date or move to a surrogate, enforce a single open interval, and update every query that treated presence as truth.
Weigh temporal rows against an event log plus projection, and be explicit about the long-term costs: growth and retention, weakened referential integrity to a period, and the authorisation risk of an as-of predicate that someone forgets.
## The test: what determines the attribute Ask which key values the attribute depends on. If `grade` is determined only by knowing *both* `student_id` and `course_id`, it is an attribute of the relationship, and normalisation says it must sit in the relation whose key is that pair — the junction table. Putting it on `student` would require one column per course; putting it on `course` would require one per student. Both are the repeating-group anti-pattern that first normal form exists to forbid. The same reasoning covers `order_line.quantity` (depends on order and product), `membership.role` (depends on user and group), `assignment.allocation_percent` (depends on person and project), and `enrollment.enrolled_on`. By contrast, `course.credits` depends on the course alone and belongs on `course`; `student.email` belongs on `student`. A column that is repeated identically across every row for one parent is a signal it was misplaced onto the link. ## The associative entity Once the junction table carries attributes it is usually called an associative entity: still a link, but with substance. It can have check constraints (`grade BETWEEN 0 AND 100`), defaults, audit columns, and its own not-null rules. Nothing about the key choice has to change yet — `PRIMARY KEY (student_id, course_id)` still expresses "at most one enrolment per pair". ## Where history breaks the model The composite key encodes a hidden business assumption: the pair happens **at most once, ever**. Real systems eventually contradict it — a student re-takes a failed course, an employee rejoins a team, a subscription is cancelled and restarted, a price on an order line changes and both versions matter. The first symptom is usually an application "upsert" that overwrites the previous row, silently destroying history, or a duplicate-key error in production that nobody predicted. ## Shape 1 — temporal link rows Add a validity period and make it part of identity: ``` membership(user_id, group_id, valid_from, valid_to NULL, role) PRIMARY KEY (user_id, group_id, valid_from) ``` Key properties to get right: - **Open interval for "current".** `valid_to IS NULL` (or a sentinel far-future date) marks the live row. Sentinels make range comparisons simpler; NULLs make "is it current" simpler. Pick one and be consistent. - **No overlaps.** Two live memberships for the same pair is corruption. Engines with range types and exclusion constraints can enforce non-overlap declaratively; otherwise the practical approximation is a partial unique index on `(user_id, group_id)` restricted to rows where `valid_to IS NULL`, which at least guarantees one current row. - **Queries get harder.** Every read must decide "as of when". "Current members" becomes a predicate; historical reporting becomes a period join. Plan indexes accordingly — typically `(user_id, group_id, valid_from DESC)` and a filtered index for the current rows. - **Attribute changes create rows.** Changing a role now means closing one interval and opening another, which must happen in one transaction. ## Shape 2 — event log plus projection Append-only `membership_event(user_id, group_id, event_type, occurred_at)` with a derived `membership_current` table maintained by the application or by triggers. This gives a perfect audit trail and simple writes, at the cost of a derived structure that can drift and needs a rebuild path. It suits systems that already think in events; it is overkill for a schema whose only requirement is "we want to see past memberships". ## Shape 3 — surrogate-keyed link Drop the natural key to a unique constraint that includes the period, and give the link its own `id`. This is what you want when other tables reference an individual link — approvals attached to a specific assignment, payments attached to a specific enrolment — because they can carry one narrow column instead of three. ## Consequences to call out - **Uniqueness semantics change.** "One row per pair" becomes "one *current* row per pair", and that must be enforced, not assumed. Most bugs in temporal link tables are two open intervals. - **Referential integrity to a period is weak.** A foreign key can point at a link row but cannot express "and it must still be valid"; that check lives in application logic or a trigger. - **Deletes turn into closures.** Removing a membership becomes setting `valid_to`, which means every existing query that assumed presence implies membership must add the period predicate. Missing one is a permission leak. - **Volume grows.** History tables only grow; plan retention, partitioning by period, or archiving before this becomes an operational surprise. ## Interview framing The strong answer states the dependency test first (attribute determined by both keys → link row), then says that history is an *identity* change rather than an extra column, then names one enforcement mechanism for non-overlap and one query consequence. Candidates who add `valid_from`/`valid_to` without saying how they prevent two open rows are describing the bug, not the design.
- With valid_from/valid_to on a membership table, how do you stop two overlapping rows for the same pair?The cheap and portable half is a unique index restricted to open rows, which guarantees at most one current membership per pair. Full non-overlap across closed intervals needs either an exclusion constraint over a range type, where the engine supports it, or a trigger that checks for intersecting periods while holding a lock on the pair. Doing the check in application code without serialising on the pair loses the race under concurrency.
- How does introducing history change existing queries against the link table?Every query that previously treated the presence of a row as "the relationship holds" now has to add an as-of predicate, typically valid_to IS NULL for current state. Missing that predicate on an authorisation check turns a revoked membership into a live one, so it is a security-relevant change, not a cosmetic one. It is usually worth exposing a current-rows view and pointing existing callers at it rather than editing every query by hand.
A junction row is a contract between two parties: the terms (rate, start date, role) are written on the contract, not on either party. Keeping history means you file each contract rather than overwriting the last one — and you must make sure only one is in force at a time.
saying these in an interview costs you the question
- Putting a relationship attribute such as a grade on one of the parent tables
- Adding valid_from/valid_to without any rule preventing two open rows for the same pair
- Assuming a soft-delete flag on a composite-keyed link table lets the pair be re-created later
- Treating history as an extra column rather than a change to what identifies a row
- Leaving existing queries unchanged after adding periods, so revoked relationships still read as active