What are the ways to implement a one-to-one relationship between two tables, and when is splitting the attributes across two tables worth it instead of keeping one wider table?

## What one-to-one means A one-to-one (1:1) relationship means a row on each side relates to at most one row on the other. It is really 1:N with an added uniqueness restriction, which is why every implementation is a foreign key plus a way of making that foreign key unique. ## Option 1 — do not split at all If every parent has exactly one child and the columns are read together, 1:1 is a sign the attributes describe the same entity and belong in one row. Splitting introduces a join on every read, a second insert on every create, and a second place for the transaction to fail. Interviewers expect you to say this first; jumping straight to two tables without asking why is a modelling smell. ## Option 2 — shared primary key The child table uses the parent's key as its own: ``` user_profile(user_id PK, REFERENCES user_account(id), bio, avatar_url) ``` Properties: - **Uniqueness is structural.** The primary key already forbids two profiles for one user; no extra constraint or index is needed. - **Joins are cheap.** Key-to-key equality, both sides indexed by their primary key, and clustered storage engines physically co-locate by that key. - **No surrogate sprawl.** There is only one identity in the system for this entity; nothing has to decide whether to reference `user_account.id` or `user_profile.id`. - **Direction is explicit.** The child's existence depends on the parent, which is usually the truth of the model. This is the standard choice for attribute extension, and also for subtype tables where each subtype row extends a supertype row. ## Option 3 — unique foreign key The child has its own surrogate primary key plus a unique constraint on the foreign key: ``` employee(id PK, ..., parking_space_id BIGINT UNIQUE REFERENCES parking_space(id)) ``` Enforcement is equally strong: the unique index makes the relationship at most one-to-one. Reasons to prefer it: - The child is an independent entity that predates or outlives the pairing (a parking space exists whether or not it is assigned). - Other tables already reference the child by its own id. - The relationship might later become 1:N, in which case you drop the unique constraint rather than restructuring keys. Which side holds the key matters: put it on the side that is **optional**, so the column can be nullable and you avoid rows that exist only to hold a NULL. If only some employees have a parking space, the key goes on `employee` (or on a separate assignment table if both sides are optional and sparse). ## Deciding to split Good reasons: - **Optionality.** Only a minority of parents have the extra attributes. Splitting avoids a wide row full of NULLs and keeps the main table dense. - **Size and temperature.** Large text, JSON documents or binary payloads inflate the hot table, hurt sequential scans and, in engines that store oversized values out of line, still cost extra I/O. Moving them to a side table keeps the frequently scanned row narrow. - **Access pattern divergence.** One part is read on every request and the other once a month; one part is updated constantly and the other is immutable. Splitting reduces write amplification, index churn and row-version pressure under MVCC. - **Security.** Sensitive attributes (national id, salary) in a separate table can be granted separately, and the base table can be exposed without them. - **Ownership.** Different teams or modules own different attribute sets, and you want the schema boundary to match. Bad reasons: "the table has too many columns", "it looks cleaner", or an ORM's inheritance defaults chosen without thought. ## Costs of splitting - Every read of the full entity is a join; every create is two inserts in one transaction. - Absence is now ambiguous: a missing child row and a child row full of NULLs mean different things and code must handle both. - Enforcing **mandatory on both sides** is genuinely hard. Two foreign keys pointing at each other create a chicken-and-egg problem for inserts, solvable only with deferrable constraints checked at commit (where supported), or a trigger, or by accepting that the rule lives in the application's transaction boundary. Be honest about this in an interview: most schemas enforce parent-requires-child in code, not in DDL. - Reporting queries need to remember the join, and an accidental inner join silently drops parents that have no child. ## Which to pick Default order: one table → shared primary key → unique foreign key. Choose the shared primary key when the child is a dependent extension of the parent; choose the unique foreign key when the child is an entity in its own right or the pairing may loosen to 1:N later. Whichever you choose, name the enforcement explicitly — an unenforced 1:1 that relies on application discipline will eventually hold two child rows, and the query that returns duplicated parents will be blamed on the join.