One-to-Many and Many-to-Many

The two shapes of relationships

Almost every schema is built from two relationship shapes:

One-to-many. One customer has many orders, one order belongs to one customer. You already built this in lesson 5-1: put a foreign key on the many side (orders.customer_id).

Many-to-many. One post can have many tags, and one tag can label many posts. Neither side can hold a single foreign key, because each side needs several pointers. The fix is a third table, a junction table (also called a join table), where each row records one connection:

CREATE TABLE post_tags (
  post_id INTEGER,
  tag_id INTEGER
);

You met one already without the name: enrollments in lesson 6-2 connected students to courses many-to-many, and carried extra data about the connection (the grade).

Reading a many-to-many through its junction table

The double join walks outward from post_tags to both real tables, turning pairs of ids into readable title-and-tag lines.

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT
);

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE post_tags (
  post_id INTEGER,
  tag_id INTEGER
);

INSERT INTO posts VALUES (1, 'SQL tips');
INSERT INTO posts VALUES (2, 'Coffee guide');

INSERT INTO tags VALUES (1, 'tech');
INSERT INTO tags VALUES (2, 'food');
INSERT INTO tags VALUES (3, 'beginner');

INSERT INTO post_tags VALUES (1, 1);
INSERT INTO post_tags VALUES (1, 3);
INSERT INTO post_tags VALUES (2, 2);
INSERT INTO post_tags VALUES (2, 3);

SELECT posts.title, tags.name
FROM post_tags
JOIN posts ON posts.id = post_tags.post_id
JOIN tags ON tags.id = post_tags.tag_id
ORDER BY posts.title, tags.name;

Output

Coffee guide|beginner
Coffee guide|food
SQL tips|beginner
SQL tips|tech

Four rows in post_tags produce four output rows. Each post carries two tags and the beginner tag is shared by both posts, which is exactly the situation no single foreign key could express.

This is the same three-table shape as the enrollments join in lesson 6-2, and that is the point. A junction table always reads this way, whichever domain it models.

Modeling playlists and songs

A playlist holds many songs and a song appears in many playlists, so the schema needs a junction table, one row per song-in-playlist connection.

Both directions are "many", which is what rules out a single foreign key on either side. A playlist_id column on songs would allow each song into only one playlist, and a song_id column on playlists would allow each playlist only one song. One column cannot hold a list of ids.

CREATE TABLE playlist_songs (
  playlist_id INTEGER REFERENCES playlists(id),
  song_id INTEGER REFERENCES songs(id)
);

The structure is identical to post_tags, with different column names. A junction table also has a natural place for facts about the connection itself, such as the position of a song within a playlist or the date it was added, which belong to neither table on its own.

Tags used on more than one post

The GROUP BY and HAVING pattern from lesson 4-3, applied to a junction table.

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT
);

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE post_tags (
  post_id INTEGER,
  tag_id INTEGER
);

INSERT INTO posts VALUES (1, 'SQL tips');
INSERT INTO posts VALUES (2, 'Coffee guide');

INSERT INTO tags VALUES (1, 'tech');
INSERT INTO tags VALUES (2, 'food');
INSERT INTO tags VALUES (3, 'beginner');

INSERT INTO post_tags VALUES (1, 1);
INSERT INTO post_tags VALUES (1, 3);
INSERT INTO post_tags VALUES (2, 2);
INSERT INTO post_tags VALUES (2, 3);

SELECT tags.name, COUNT(*) AS posts_tagged
FROM post_tags
JOIN tags ON tags.id = post_tags.tag_id
GROUP BY tags.name
HAVING COUNT(*) >= 2
ORDER BY tags.name;

Output

beginner|2

Counting the junction rows in each tag bucket counts how many posts carry that tag, and HAVING COUNT(*) >= 2 keeps only the buckets that clear the threshold. tech and food appear once each and are filtered out, leaving beginner.

The posts table is not joined at all here, because the count only needs the link rows. Junction tables are often queried this way, on their own or with one side attached, and adding the second real table would only cost time.