Simplified
Simplified

L5.1When neither table can hold the key

YOU

When neither table can hold the key

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
usersidname1Ada2Ben3Cy4Dee5Eli6Faylessonsidtitle1What a database is2Tables3Reading data
usersidnamelesson_idlesson_id2lesson_id3…1Ada2Ben3Cy4Dee5Eli6Faylessonsidtitle1What a database is2Tables3Reading data4Changing data
usersidname1Ada2Ben3Cy4Dee5Eli6Faylessonsidtitleuser_iduser_id2…1What a database is2Tables3Reading data
usersidname1Ada2Ben3Cy4Dee5Eli6Faymanylessonsidtitle1What a database is2Tables3Reading datamany
many-to-many
a row on either side can match many rows on the other
usersidname1Ada2Ben3Cy4Dee5Eli6Faymanylessonsidtitle1What a database is2Tables3Reading datamany?
playlistsidname901Gym905Chillsongsidtitle126Espresso210Birds of a Feather340Not Like Us
playlistsidnamesong1song2…901Gym126340905Chill126210songsidtitleplaylist1playlist2…126Espresso901905210Birds of a Feather905340Not Like Us901
usersidname1Ada2Ben3Cy4Dee5Eli6Faymanylessonsidtitle1What a database is2Tables3Reading datamany

Databases · Lesson 5.1

Many-to-Many

When neither table can hold the key

Simplified
0:00 / 2:25

So JOIN and ON put each answer beside the name of the learner who gave it.

That worked because each attempts row holds one user ID, from its learner's row.

Ada gave three answers, so her id, 1, sits in three attempts rows.

One learner, many attempts: that's one-to-many, with the foreign key on the many side.

usersidname1Ada2Ben3Cy4Dee5Eli6Fayattemptsiduser_idlesson_id111212321413541622741

Simplified also saves every lesson a learner finishes.

Ada has finished two lessons: 1, What a database is, and 2, Tables.

Dee has finished one: lesson 1, What a database is.

learnerslessonsAdaBenCyDeeEliFayWhat a database isTablesReading data

So which table gets the foreign key, to save who finished which lesson?

Say users gets a lesson ID column, the way attempts got a user ID.

Ada's row has one lesson ID cell, so it holds 1.

But she finished lesson 2 too, and that cell is already full.

So users would need lesson ID 1, lesson ID 2, lesson ID 3, and so on.

Ada fills two of them, Dee fills one, and the rest sit empty.

And Simplified keeps adding lessons. So when does it stop?

usersidnamelesson_id1lesson_id2lesson_id3…1Ada12—…2Ben———…3Cy———…4Dee1——…5Eli———…6Fay———…

So can lessons hold the foreign key instead?

Say lessons gets a user ID column.

Lesson 1's row holds Ada's id, 1.

But Dee finished lesson 1 too, so it needs a second user ID column, for her 4.

And with 2,000,000 learners, lesson 1 could need 2,000,000 columns.

lessonsidtitleuser_id1user_id2…1What a database is14…2Tables1—…3Reading data——…

So here, both sides are the many side.

One learner finishes many lessons: Ada finished two.

And one lesson is finished by many learners: lesson 1, by Ada and Dee.

This is called a many-to-many relationship.

Many-to-many means a row on either side can match many rows on the other.

So neither table can hold the foreign key: each row would need a column per match.

learnerslessonsAdaBenCyDeeEliFayWhat a database isTablesReading data2 lessons2 learners

Now what about a playlist on Spotify?

playlistssongsGymChillEspressoNot Like UsBirds of a Feather

Your Gym playlist holds Espresso and Not Like Us.

Your friend's Chill playlist holds Espresso and Birds of a Feather.

So one playlist holds many songs, and Espresso is in two playlists.

So Spotify might have a many-to-many relationship between playlists and songs.

A song column in playlists would need song1, song2, and so on.

And a playlist column in songs would need one for every playlist a song is in.

So in a many-to-many relationship, neither table can hold the foreign key.

Example: When neither table can hold the key

On GitHub, one account can star many repositories, and one repository can be starred by many accounts.

What kind of relationship is that between accounts and repositories?

Explanation

First, one account stars many repositories, so the accounts side matches many.

Then, one repository is starred by many accounts, so the repositories side matches many too.

Therefore, it's many-to-many.

Question 1

On Uber, one rider takes many trips, and each trip has exactly one rider.

Which kind of relationship is it between riders and trips?

A third table for the pairs

usersidname1Ada2Ben3Cy4Dee5Eli6Faylessonsidtitle1What a database is2Tables3Reading data
usersidnamelesson_idlesson_id2lesson_id3…1Ada12Ben3Cy4Dee5Eli6Faylessonsidtitle1What a database is2Tables3Reading data
usersidname1Ada2Ben3Cy4Dee5Eli6Faylessonsidtitleuser_iduser_id2…1What a database is12Tables3Reading data
usersidname1Ada2Ben3Cy4Dee5Eli6Fayuser_idlesson_id11124121lessonsidtitle1What a database is2Tables3Reading data
join table
holds one row for each pair, with a foreign key to each side
usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id11124121lessonsidtitle1What a database is2Tables3Reading data
usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_idcompleted_at112026-11-02 10:15122026-11-03 18:40412026-11-04 08:05lessonsidtitle1What a database is2Tables3Reading data
playlistsidname901Gym905Chillplaylist_songsplaylist_idsong_idadded_at9011262026-10-019013402026-10-039051262026-10-129052102026-10-20songsidtitle126Espresso210Birds of a Feather340Not Like Us
usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id11124121lessonsidtitle1What a database is2Tables3Reading data

Databases · Lesson 5.2

Many-to-Many

A third table for the pairs

Simplified
0:00 / 3:05

So Ada finished lessons 1 and 2, and Dee finished lesson 1.

But a lesson ID column in users fills up after one lesson.

And a user ID column in lessons fills up after one learner.

learnerslessonsAdaBenCyDeeEliFayWhat a database isTablesReading data2 lessons2 learners

So where can Simplified save who finished which lesson?

In a third table, with one row for each finish.

Every time a learner finishes a lesson, Simplified adds one row to it.

Say the table is called completions.

Each row holds two ids: the learner's user ID, and the lesson's lesson ID.

Ada finished two lessons, so she has two rows: 1 and 1, then 1 and 2.

Dee finished one, so she has one row: 4 and 1.

So completions holds three rows, one for each learner and lesson that go together.

completionsuser_idlesson_id111241

So how does each row point at its learner and its lesson?

user ID holds an id from users, so it's a foreign key that REFERENCES users.

lesson ID holds an id from lessons, so it's a foreign key that REFERENCES lessons.

So Postgres refuses a row whose user ID or lesson ID matches no row.

This is called a join table.

A join table holds one row for each pair, with a foreign key to each side.

usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data

So how does a third table fix the column-per-finish problem?

Look at users and completions on their own.

One learner can have many completions rows: Ada has two.

But each completions row has one user ID, so it points at one learner.

That's one-to-many, with the foreign key on the many side, completions.

usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data

And lessons and completions are one-to-many too.

Lesson 1 has two completions rows: Ada's and Dee's.

Each row has one lesson ID, so it points at one lesson.

So a many-to-many relationship is two one-to-many relationships, meeting in the join table.

And a new finish is just one new row, never a new column.

usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data

And where does the time Ada finished lesson 1 go?

She finished it at 10:15 am on November 2.

It can't go in Ada's row in users: she finished lesson 2 at another time.

And it can't go in lesson 1's row: Dee finished it at another time.

The time belongs to the pair: Ada and lesson 1.

So it goes in their completions row, in a completed at column.

completionsuser_idlesson_idcompleted_at112026-11-02 10:15122026-11-03 18:40412026-11-04 08:05

So how might Spotify keep your playlists?

playlistsidname901Gym905Chillplaylist_songsplaylist_idsong_idadded_at9011262026-10-309013402026-10-309051262026-11-039052102026-11-03songsidtitle126Espresso340Not Like Us210Birds of a Feather

Say Spotify keeps playlists in one table and songs in another.

Then a join table, playlist songs, holds one row for each song in each playlist.

Your Gym playlist, 901, holds Espresso, 126, and Not Like Us, 340: two rows.

Your friend's Chill playlist, 905, holds Espresso and Birds of a Feather, 210: two more.

Spotify shows the date you added each song to a playlist.

That date belongs to the pair, so it might sit in playlist songs, as added at.

So a join table turns each pair into one row, with a foreign key to each side.

Example: A third table for the pairs

Simplified's completions table holds one row each time a learner finishes a lesson: (user_id, lesson_id) rows 1 and 1, 1 and 2, and 4 and 1. Then Ben, whose id is 2, finishes lesson 2, and then lesson 3.

Which rows does completions gain?

Explanation

First, each finish adds one row, holding the learner's user_id and the lesson's lesson_id.

Then, Ben's id is 2, and he finishes lessons 2 and 3.

Therefore, completions gains two rows: 2 and 2, then 2 and 3.

Question 1

Simplified keeps a join table, completions, with one row each time a learner finishes a lesson. It starts empty. Then Ada finishes 3 lessons, Ben finishes 2, and Dee finishes 4.

How many rows does completions hold?

rows

Question 2

Simplified's users table holds one row for each learner. Its completions table holds one row each time a learner finishes a lesson, with user_id and lesson_id. Ada has one row in users, and then finishes 5 lessons.

How many rows does users hold for Ada afterwards?

rows

Question 3

Say Spotify keeps playlists in one table, songs in another, and a join table, playlist_songs, with one row for each song in each playlist. Each playlist shows the date each song was added to it.

Where does that date belong?

Question 4

Say Netflix keeps profiles in one table and shows in another, each with an id primary key. A profile's My List holds many shows, and a show is on many profiles' lists. A join table, my_list, holds one row for each show on each profile's list, with columns profile_id and show_id.

Which of those columns are foreign keys?

Question 5

Simplified's completions table holds one row each time a learner finishes a lesson. Its (user_id, lesson_id) rows are: 1 and 1, 1 and 2, 4 and 1, 2 and 1, and 2 and 3.

How many completions rows point at lesson 1?

rows

Question 6

Simplified's lessons table holds ids 1, 2 and 3. Its completions table's lesson_id is a foreign key: lesson_id integer REFERENCES lessons (id). Its server sends INSERT INTO completions (user_id, lesson_id) VALUES (1, 9);.

What happens?

Assistant

Highlight lesson text and click Ask AI, or type a question below.