DEE
Database Engineering Essentials

English

繁體中文

English

繁體中文

Appearance

[DEE-103] Relationships (1:1, 1:N, M:N) ​

INFO

Model every entity relationship explicitly using the correct cardinality pattern. Use foreign keys for 1:1 and 1:N; use a junction table for M:N. Never store comma-separated IDs.

Context ​

Relational databases model the real world as entities (tables) connected by relationships. The three fundamental cardinality types -- one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N) -- determine how foreign keys and tables are structured. Choosing the wrong pattern leads to data anomalies, query complexity, and integrity gaps.

Principle ​

  • You MUST use a foreign key on the "many" side for 1:N relationships.
  • You MUST use a junction (join/bridge) table for M:N relationships.
  • You SHOULD implement 1:1 relationships by placing the FK on the table that is optional or secondary.
  • You MUST NOT store multiple IDs in a single column (comma-separated, JSON array, etc.) as a substitute for a proper junction table.
  • You MUST index all foreign key columns used in relationships.

Visual ​

One-to-One (1:1) ​

A 1:1 relationship means each row in table A corresponds to at most one row in table B. This is implemented by placing a foreign key with a UNIQUE constraint on one side.

When to use 1:1:

  • Separating rarely-accessed or large columns (e.g., profile bio, avatar blob) from a frequently-queried core table
  • Isolating optional data that not every row will have
  • Enforcing access control at the table level (different tables, different permissions)
sql
CREATE TABLE users (
    user_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email    TEXT NOT NULL UNIQUE,
    name     TEXT NOT NULL
);

-- Profile is the "optional" side: not every user may have one
CREATE TABLE profiles (
    profile_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     BIGINT NOT NULL UNIQUE
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  TEXT
);

The UNIQUE constraint on user_id in profiles enforces the 1:1 cardinality -- each user can have at most one profile.

One-to-Many (1:N) ​

A 1:N relationship means one row in the parent table relates to zero or more rows in the child table. The foreign key lives on the "many" (child) side.

sql
CREATE TABLE posts (
    post_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    title      TEXT NOT NULL,
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Index the FK for join and delete performance
CREATE INDEX idx_posts_user_id ON posts(user_id);

One user writes many posts. Deleting a user cascades to their posts (appropriate if posts have no independent archival value; use RESTRICT if they do).

Many-to-Many (M:N) ​

A M:N relationship means rows in table A relate to multiple rows in table B, and vice versa. Relational databases cannot represent this directly -- you MUST use a junction table (also called a join table, bridge table, or associative entity).

sql
CREATE TABLE tags (
    tag_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    TEXT NOT NULL UNIQUE
);

-- Junction table: each row represents one post-tag association
CREATE TABLE post_tags (
    post_id  BIGINT NOT NULL REFERENCES posts(post_id) ON DELETE CASCADE,
    tag_id   BIGINT NOT NULL REFERENCES tags(tag_id)   ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- The composite PK already indexes (post_id, tag_id).
-- Add a reverse index for queries that start from tag_id.
CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);

Junction Tables with Extra Attributes ​

When the relationship itself carries data, add columns to the junction table:

sql
CREATE TABLE course_enrollments (
    student_id   BIGINT NOT NULL REFERENCES students(student_id),
    course_id    BIGINT NOT NULL REFERENCES courses(course_id),
    enrolled_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    grade        CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

Here enrolled_at and grade are properties of the enrollment (the relationship), not of the student or the course.

Relationship Summary ​

CardinalityFK PlacementKey ConstraintExample
1:1On the optional/secondary tableFK with UNIQUEusers -- profiles
1:NOn the "many" (child) tableFK (no UNIQUE)users -- posts
M:NJunction table with two FKsComposite PK on both FKsposts -- post_tags -- tags

Common Mistakes ​

MistakeWhy It HurtsFix
Comma-separated IDs in a column (e.g., tags = '1,3,7')Cannot enforce FK integrity, cannot index, cannot join efficiently, breaks 1NFUse a junction table
JSON array of IDs (e.g., tag_ids jsonb)Same problems as CSV plus storage overhead; jsonb GIN indexes help reads but do not enforce integrityUse a junction table; reserve JSON for truly schemaless data
Unnecessary 1:1 splitsSplitting a table "just in case" adds join overhead with no benefitOnly split when you have a measurable reason (column size, access pattern, security)
Missing FK index on the "many" sideJOIN and CASCADE DELETE performance degrades to sequential scansAlways create an index on FK columns
M:N without a composite PKAllows duplicate associations (e.g., the same tag applied to the same post twice)Use PRIMARY KEY (a_id, b_id) on the junction table
Using a surrogate PK on a simple junction tableAdds a useless column and a useless index; the composite FK pair is already uniqueOnly add a surrogate PK if the junction row is referenced by other tables
Bidirectional FKs for 1:1Circular references require deferred constraints and complicate insertsPut the FK on one side only; use UNIQUE to enforce cardinality

References ​