Databases · Lesson 5.1
When neither table can hold the key
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.
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.
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?
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.
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.
Now what about a playlist on Spotify?
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.
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.
On Uber, one rider takes many trips, and each trip has exactly one rider.
Which kind of relationship is it between riders and trips?
Databases · Lesson 5.2
A third table for the pairs
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.
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.
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.
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.
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.
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.
So how might Spotify keep your playlists?
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.
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.
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?
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?
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?
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?
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?
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?