Cardinality — One-to-Many and Many-to-Many

~14 min · relationships, cardinality, junction-table

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The two relationship shapes you'll model 99% of the time

Almost every relational schema is built from two relationship cardinalities:

One-to-many — one author has many posts. Implemented with a foreign key on the 'many' side.
Many-to-many — posts have many tags, tags belong to many posts. Implemented with a junction table (also called join table, link table, association table).

One-to-one is rare — usually a sign you should merge the tables, unless you're deliberately splitting hot/cold columns.

Principle: When you find yourself wanting to put a comma-separated list of ids into a TEXT column, stop. That's a junction table screaming to exist. SQL has no good answer for 'find all rows where this column contains 42'; junction tables turn it into an indexed JOIN.

Code

One-to-many — authors and posts·sql

CREATE TABLE authors (
  id INTEGER PRIMARY KEY, name TEXT NOT NULL
) STRICT;

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title TEXT NOT NULL
) STRICT;

CREATE INDEX idx_posts_author ON posts(author_id);  -- almost always wanted

Many-to-many — posts and tags via a junction table·sql

CREATE TABLE tags (
  id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
) WITHOUT ROWID;

-- 'all posts tagged sql':
SELECT p.id, p.title FROM posts p
INNER JOIN post_tags pt ON pt.post_id = p.id
INNER JOIN tags t       ON t.id      = pt.tag_id
WHERE  t.name = 'sql';

External links

Relationships and cardinality (overview)

Exercise

Design a many-to-many: users can favorite posts. Build the three tables (users, posts, favorites), then write four queries: (1) all posts a given user has favorited, (2) all users who favorited a given post, (3) the top-10 most-favorited posts, (4) users with no favorites. Note where you needed an index for performance.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousMulti-Table Joins and Self-Joins Next →ON DELETE — Cascade, SET NULL, RESTRICT

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.