Skip to content
Tutorials Center
My learning

Topics

Lesson 79 of 125 Β· Designing Databases

One-to-Many and Many-to-Many

Model real systems in SQL. Build one-to-many, one-to-one and many-to-many relationships with foreign keys and junction tables, then query them both ways.

Real data is connected. A blog post has an author and many comments, a post carries several tags, an employee works on several projects, and a user follows other users. In a relational database you express every one of these connections with the same small toolkit: foreign keys, and tables made of foreign keys called junction tables.

In Entity-Relationship Modeling you learned to draw relationships, and in Normalization you saw why each fact needs a single home. This lesson is hands-on: you'll build each kind of relationship, choose sensible delete rules and indexes, write the queries every application needs ("all tags of this post", "all posts with this tag", "posts with all of these tags"), and model a small but realistic system from scratch.

All examples turn on foreign key enforcement with PRAGMA foreign_keys = ON;. SQLite leaves it off by default for backward compatibility, while PostgreSQL, MySQL (InnoDB) and SQL Server always enforce foreign keys. The Foreign Keys lesson covers the details.

One-to-many: the foreign key lives on the "many" side

One author writes many posts, and each post has one author. A column can hold only one value, so the link must be stored on the side that has exactly one partner: each posts row stores its author_id. The "one" side (authors) doesn't change at all.

Two decisions come with every one-to-many relationship:

  1. Is the parent required? If every post must have an author, make the foreign key NOT NULL. If a child can exist alone (a task not yet assigned to anyone), allow NULL.
  2. What happens when the parent is deleted? ON DELETE CASCADE deletes the children too, ON DELETE SET NULL detaches them, and the default (NO ACTION, or RESTRICT) refuses to delete a parent that still has children.

Choose the delete rule by asking what the child means without its parent. Comments make no sense without their post, so cascading is right. Posts are valuable on their own, so deleting an author who still has posts should be blocked. Run this example; the last statement fails on purpose:

SQL Authors, posts and comments

Press Run (or Ctrl + Enter) to see the result here.

After the post is deleted, only two comments remain: both comments on post 1 went with it. The final DELETE stops with Error: FOREIGN KEY constraint failed, because Ines still has a post.

Index your foreign keys. SQLite and PostgreSQL don't create an index for a foreign key column automatically (MySQL's InnoDB does). Without one, "all posts by author 7" scans the whole posts table, and so does every parent delete, because the database must look for children. Adding CREATE INDEX ... ON posts (author_id) is almost always right. See Indexes.

Many-to-many: the junction table

A post can have many tags, and a tag can label many posts. Neither table can hold the link, so you create a third table with one row per pairing. It goes by many names: junction table, join table, link table, bridge table, or associative entity.

A good junction table has:

  • Two foreign keys, both NOT NULL, usually with ON DELETE CASCADE (a pairing is meaningless once either side is gone).
  • A composite primary key on the pair, which also prevents duplicate pairings.
  • An index for the other direction. The primary key (post_id, tag_id) makes "tags of a post" fast because post_id comes first. "Posts with a tag" needs a second index that starts with tag_id.

The queries below are the ones nearly every app needs:

SQL Posts and tags

Press Run (or Ctrl + Enter) to see the result here.

The last query is a famous pattern called relational division: filter to the wanted tags, group by post, and keep only posts that matched every tag (COUNT(*) = 2, the number of tags in the list). It returns "Reading Query Plans" and "Covering Indexes". Using IN alone would answer a different question, posts with any of the tags, and would also return "Window Functions by Example".

Other everyday questions use joins you already know: an anti-join finds tags no post uses, and EXISTS finds posts that have some tag without producing duplicate rows.

Composite key or surrogate id? Some frameworks prefer every table to have its own id column, including junction tables. That's fine, but then you must still add UNIQUE (post_id, tag_id), or duplicate pairings will slip in and quietly double your counts.

Junction tables with their own columns

The link itself often carries information. An employee works on a project in some role and for some hours per week. An order contains a product in some quantity at some price. Those columns describe the pairing, so they belong in the junction table. The company sample database has exactly this design: assignments (employee_id, project_id, role, hours_per_week).

SQL A junction table with data Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

Wen Kaya (60 hours), Ximena Yilmaz (50) and Aiden Costa (45) are booked for more than 40 hours a week. The second query reads the same table from the project side; SUM(a.role = 'Lead') counts leads because a comparison in SQLite evaluates to 1 or 0. The Design System project, with 4 leads among 5 people, may deserve a closer look.

The shop database's order_items and the school database's enrollments (with a grade and a term) are junction tables in the same spirit. Once you start looking, you'll find that the most important tables in a business are often junction tables: orders lines, bookings, enrollments, memberships.

One-to-one: share the primary key

One-to-one relationships are rarer. In Entity-Relationship Modeling you implemented one with a unique foreign key. A second, tidy option is a shared primary key: the child's primary key is the foreign key. Each user has at most one profile, and a profile can't exist without its user:

SQL A shared primary key

Press Run (or Ctrl + Enter) to see the result here.

Tariq has no profile, so his columns are NULL. The final insert fails with Error: UNIQUE constraint failed: user_profiles.user_id, because a primary key is unique by definition. Use a 1:1 split when the extra columns are optional, large, rarely needed, or protected by different permissions. Otherwise, just put the columns in the main table.

Self-referencing relationships

Both relationship shapes can point back to the same table.

One-to-many on itself gives you trees: categories with parent categories, employees with managers (employees.manager_id in the company database), comment threads with replies. A single nullable column such as parent_id INTEGER REFERENCES categories (id) does it.

Many-to-many on itself gives you networks: users following users, products that are "frequently bought with" other products, cities connected by flights. The junction table has two foreign keys to the same table, so name them by their role:

SQL Followers on a social network

Press Run (or Ctrl + Enter) to see the result here.

ines has 3 followers, and the mutual pairs are ines–tariq and ines–lucia. The CHECK constraint stops users from following themselves, and the condition f1.follower_id < f1.followee_id keeps each mutual pair from appearing twice. Walking a network more than one step ("friends of friends") is a job for recursive CTEs.

Modeling a real system

Let's put it together. A small team task tracker has these rules:

  • Users belong to any number of projects, with a role in each (owner, member, viewer).
  • A project has many tasks. A task belongs to one project and is assigned to at most one user (or nobody yet).
  • Tasks can carry any number of labels, and labels belong to a project.

Translated with the rules from this lesson, that's three entity tables, two junction tables, and three one-to-many foreign keys:

SQL
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

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

-- many-to-many with data: membership + role
CREATE TABLE project_members (
  project_id INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'member', 'viewer')),
  PRIMARY KEY (project_id, user_id)
);

-- one-to-many (project -> tasks), optional one-to-many (user -> tasks)
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
  assignee_id INTEGER REFERENCES users (id) ON DELETE SET NULL,
  title TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1))
);

CREATE TABLE labels (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  UNIQUE (project_id, name)
);

-- many-to-many: tasks <-> labels
CREATE TABLE task_labels (
  task_id INTEGER NOT NULL REFERENCES tasks (id) ON DELETE CASCADE,
  label_id INTEGER NOT NULL REFERENCES labels (id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, label_id)
);

Notice the delete rules tell a story: deleting a project wipes its tasks, labels and memberships, while deleting a user unassigns their tasks (SET NULL) instead of destroying the team's work. Paste the schema into a TryIt, add a few rows, and try queries such as "open tasks per assignee in project 1" or "tasks with the label bug that nobody owns".

Patterns to avoid

  • Lists in a column (tag_ids = '1,4,7'). No foreign keys, no indexes, painful queries. Use a junction table.
  • Numbered columns (phone1, phone2, phone3). There's always a fourth. Use a child table.
  • Polymorphic associations (commentable_type = 'post', commentable_id = 42) where one column points to different tables depending on another column. Some frameworks encourage this, but no foreign key can protect it, so orphans creep in. Prefer one nullable foreign key per possible parent with a CHECK that exactly one is set, or one junction table per parent type.
  • Duplicated links. Storing author_id on comments and deriving it through post_id creates two paths that can disagree. Store each relationship once.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. Each invoice belongs to one customer, and a customer can have many invoices. How do you store this?

    One-to-many relationships put the foreign key on the many side. A junction table would also allow an invoice to belong to several customers, which the rules forbid.
  2. 2. post_tags has PRIMARY KEY (post_id, tag_id). Which query benefits from an extra index on tag_id?

    The primary key index starts with post_id, so it helps lookups by post. Lookups by tag need an index whose first column is tag_id.
  3. 3. What does this query return? SELECT post_id FROM post_tags WHERE tag_id IN (1, 2) GROUP BY post_id HAVING COUNT(*) = 2;

    After filtering to tags 1 and 2, a post can only reach a count of 2 if it has both (the primary key prevents the same pair twice). This is relational division.
  4. 4. Deleting a user should keep their tasks but leave them unassigned. Which foreign key rule fits tasks.assignee_id?

    SET NULL detaches the children instead of deleting them, which requires the column to allow NULL.
  5. 5. Where should the number of hours an employee spends on a project be stored?

    The hours describe one employee on one project, so they belong to the pairing, which is the junction table row.

Summary

  • One-to-many: put a foreign key on the many side; make it NOT NULL if the parent is required, and index it.
  • Pick delete rules by meaning: CASCADE for dependent data, SET NULL for optional links, the default (refuse) for valuable children.
  • Many-to-many: a junction table with two foreign keys, a composite primary key (or a UNIQUE pair) and an index for the reverse direction.
  • Junction tables can carry data about the relationship: roles, quantities, prices, dates, grades.
  • One-to-one: a unique foreign key or a shared primary key; split tables only for a reason.
  • Self-references model trees (parent_id) and networks (a junction table with two keys to the same table).
  • "Has all of these" questions use relational division: WHERE ... IN (...) GROUP BY ... HAVING COUNT(*) = n.

Next: Triggers, where the database runs your SQL automatically when rows change.