Databases · Lesson 3.1
Why one table isn't enough
When you answer a question on Simplified, its server adds a row to a table called attempts.
So attempts holds one row for each answer a learner gives.
Each row holds its own id, and the lesson ID of the question's lesson.
So far it holds seven rows.
GROUP BY lesson ID counted them: 4 for lesson 1, 2 for lesson 2, 1 for lesson 3.
And users holds the learners, Ada to Fay, one row each.
But Simplified needs to know who gave each answer, so each learner's progress is their own.
And no row in attempts says who gave it.
So how does a row in attempts say who gave the answer?
Each learner already has a row in users, with their name and email.
So one way is to copy their name and email onto every row of attempts.
Ada gave three answers, so her name and email would be stored three times.
But what happens when Ada changes her name?
Say she changes it to Ada Lin.
Then every copy has to change: the one in users, and all three in attempts.
Say the server changes two copies in attempts, and misses the third.
Now attempt 4 says Ada, while users and the other two say Ada Lin.
So one learner has two names, and the data disagrees with itself.
So instead, each row in attempts stores just one thing about the learner: their id.
That's a new column in attempts, user ID.
user ID holds the id of the learner who gave the answer: the number from their row in users.
Ada's id is 1, so her three answers each hold 1.
Ben's id is 2, so his two hold 2. And Dee's two hold 4.
Now Ada's name is stored in one place: her row in users.
So when she changes it to Ada Lin, only that one cell changes.
Her three answers still hold 1, so they still point at her row.
So a good rule is: never store the same text twice.
Store it once, in its own row, and point at that row with its id.
So how might Instagram show your new username on all your old comments?
Say Instagram keeps comments in a table, one row for each comment.
Your account's id is 205, so each of your comments holds 205, not your username.
Your username is stored once, in your account's row.
So when you change it from sam.codes to sam.builds, only that one cell changes.
And all three of your comments still hold 205, so they all show sam.builds.
So attempts stores only the learner's id, and the learner's name lives in one place, users.
Simplified's users table has one row for each learner, and Dee's row holds her id, 4, and her name. Its attempts table has one row for each answer, with a user_id column for the learner who gave it. Dee answers five questions.
How many times is Dee's name stored?
Explanation
First, each of Dee's five answers adds a row to attempts, holding her id, 4, not her name.
Then, her name is stored in just one place: her row in users.
Therefore, Dee's name is stored once.
Simplified's users table holds 6 rows, one for each learner. Its attempts table holds one row for each answer, with the user_id of the learner who gave it. Ada, who has a row in users, answers 3 questions.
How many rows does users hold afterwards?
Databases · Lesson 3.2
Pointing at a row that exists
So each row in attempts holds a user ID: the id from its learner's row in users.
Ada's three answers hold 1, Ben's two hold 2, and Dee's two hold 4.
But to Postgres, user ID is just a column of whole numbers, like xp.
So any whole number fits, even one that's no learner's id.
Say a bug in Simplified's server sends an attempt with user ID 99, for lesson 2.
No row in users has id 99, but Postgres stores the attempt anyway.
So that answer points at nobody, and no learner's progress will ever show it.
So how can Postgres stop an attempt from pointing at a learner who doesn't exist?
attempts' schema gives user ID one more rule: REFERENCES users, then id in brackets.
It means every user ID must match an id in users.
This makes user ID a foreign key.
A foreign key is a column that holds another table's primary key.
users' primary key is id, so each user ID holds an id from users.
REFERENCES writes a foreign key in the schema: the table it points at, then that table's key.
So what does Postgres do with user ID 99 now?
The server sends the same INSERT: user ID 99, for lesson 2.
Before storing it, Postgres looks in users for a row whose id is 99.
No row has it, so Postgres refuses the attempt.
Nothing is stored, and attempts still holds seven rows.
Without REFERENCES, that same 99 went in; with it, 99 is refused.
And what if the attempt is Dee's?
Dee's id is 4, so this INSERT has user ID 4.
Postgres looks in users, and finds Dee's row, with id 4.
So it stores the attempt, as row 8.
So how is a foreign key different from the primary key?
The primary key names this row: no two attempts share an id.
A foreign key points at a row in another table.
And many rows can hold the same one: Ada's 1 is in three attempts.
And what about lesson ID?
It holds the id of the question's lesson, so it points at a table too.
That table is lessons: one row for each lesson in the course.
Each row holds the lesson's id and its title, like 1, What a database is.
Lesson 1 got four answers, so four attempts hold lesson ID 1.
In attempts' schema, lesson ID's line says REFERENCES lessons, then id.
So lesson ID is a foreign key too, and attempts has two: user ID, to users, and lesson ID, to lessons.
And why are they called user ID and lesson ID?
Each table's own primary key is called id.
A column that points at it is named after that table: user ID for users, lesson ID for lessons.
So when you see post ID, you can guess it points at the id of a posts table.
So how might Instagram keep each comment on the right post?
Say Instagram's comments table has a post ID column, a foreign key to posts.
You comment "So cool!" on your friend's post, whose id is 81.
So Instagram's server might send an INSERT with post ID 81.
Postgres finds post 81 in posts, so it stores your comment, under that post.
So a foreign key holds another table's primary key, and Postgres refuses one that matches no row.
Simplified's attempts table has a user_id column with REFERENCES users (id). The users table holds ids 1 to 6. Simplified's server sends Postgres INSERT INTO attempts (user_id, lesson_id) VALUES (7, 1);.
What happens to this attempt?
Explanation
First, user_id is a foreign key, so Postgres looks in users for a row whose id is 7.
Then, users holds ids 1 to 6 only, so no row matches.
Therefore, Postgres refuses the attempt, and nothing is stored.
YouTube's comments table has a video_id column. Each video_id holds a value from the id column of the videos table, which is that table's primary key.
What is video_id?
Simplified's attempts table has user_id with REFERENCES users (id) and lesson_id with REFERENCES lessons (id). users holds ids 1 to 6, and lessons holds ids 1 to 3. Its server sends INSERT INTO attempts (user_id, lesson_id) VALUES (2, 9);.
What happens?
Instagram's comments table might be written like this: id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, post_id integer NOT NULL REFERENCES posts (id), account_id integer NOT NULL REFERENCES accounts (id), and text text NOT NULL.
How many foreign keys does comments have?
A posts table has its primary key in a column called id. Its developers add a comments table, with a column that points at the id of posts.
Following the usual naming habit, what is that column called?
Simplified's attempts table holds 7 rows. Their user_id values, a foreign key to users, are 1, 1, 2, 1, 4, 2 and 4.
How many rows of attempts hold the user_id 4?
Say Simplified's attempts table had user_id integer NOT NULL, with no REFERENCES. It holds 7 rows, and users holds ids 1 to 6. Its server sends INSERT INTO attempts (user_id, lesson_id) VALUES (99, 2);.
How many rows does attempts hold afterwards?
Databases · Lesson 3.3
Which table gets the foreign key?
So attempts' user ID is a foreign key: each one must match an id in users.
Ada's id, 1, is in three attempts, and Ben's 2 is in two.
Cy's 3 isn't in any, because he hasn't answered a question yet.
So one learner can have any number of attempts: Ada has three, and Cy has none.
And can one attempt belong to two learners?
No: each attempt has one user ID cell, and a cell holds one value.
So each attempt belongs to exactly one learner.
This is called a one-to-many relationship.
One-to-many means one row goes with many rows in another table, and each of those with just one.
So users and attempts are one-to-many: one learner, many attempts.
So why does the foreign key go in attempts, and not in users?
Picture it the other way, with users holding the ids of each learner's attempts.
Ada would need three columns for hers, 1, 2 and 4, and Ben two.
And Ada's next answer would need a fourth column, then a fifth.
But each attempt has just one learner, so one user ID column is always enough.
So the foreign key goes on the many side: one cell in each attempt.
So which row has to go in first?
The one side's: a learner's row in users comes before any of their attempts.
Because the foreign key refuses a user ID that matches no learner's id.
Say a new learner will get id 7, and the server sends their first answer too soon.
Their row isn't in users yet, so no row has id 7.
So Postgres refuses the attempt, and nothing is stored.
Once their row is in users, the same attempt goes in.
A learner with no attempts is fine, though, like Cy: a one can have zero many.
And what about lessons and attempts?
Each attempt holds one lesson ID, so it's for exactly one lesson.
And lesson 1 has four attempts, lesson 2 two, and lesson 3 one.
So lessons and attempts are one-to-many too, with the key, lesson ID, in attempts.
And a lesson's row goes in before any attempt that points at it.
So how might Uber keep all your trips under your account?
Say Uber keeps trips in a table, each holding one account ID, a foreign key to accounts.
Your account's id is 512, and you've taken three trips: to the airport, the office and home.
So three rows in trips hold 512, and each trip has just one rider.
That's one-to-many: one account, many trips, with the key in trips.
And your account's row went in first, when you signed up, before your first trip.
So in one-to-many, the foreign key goes on the many side, and the one side's row comes first.
Spotify might keep albums in one table and songs in another. Each song is on exactly one album, and an album has many songs.
Which table holds the foreign key?
Explanation
First, one album goes with many songs, and each song with just one album, so it's one-to-many.
Then, the foreign key goes on the many side, where each row has room for one id.
Therefore, songs holds the foreign key, an album_id in each song.
YouTube might keep videos in one table and comments in another. Each comment is on exactly one video, and a video can have thousands of comments.
Where does the foreign key go?
Simplified's attempts table has one user_id column, a foreign key to users. Ada and Ben work out one question together, and Simplified's server wants one attempt to record both of them.
What can that one row hold?
Simplified's users table holds learners with ids 1 to 8. Its attempts table's user_id values, a foreign key to users, are 1, 1, 2, 1, 4, 2 and 4.
How many learners have no attempts at all?
WhatsApp might keep chats in one table and messages in another, with each message holding one chat_id, a foreign key to chats. Your chat with Mom holds 15 messages.
How many rows in chats are for your chat with Mom?
Spotify might keep albums in one table and songs in another, with each song's album_id a foreign key to albums. A new album with 12 songs is coming out.
In what order do its rows go in?
Uber might keep accounts in one table and trips in another, with each trip's account_id a foreign key to accounts. Ana has taken 5 trips and Ben 3, and Cara has just signed up with no trips.
How many rows in trips hold one of their three account ids?
Databases · Lesson 3.4
Whose id goes in the new row?
So an attempt sits on the many side, and its learner's row in users comes first.
Ada's row is already there: she signed up, and Postgres gave her id 1.
Now Ada answers a question in lesson 2.
She logged in with ada@simplified.org, so that's how Simplified's server knows it's her.
But the new attempt needs her user ID, 1, not her email.
So how does the server get Ada's id, to put in her attempt?
It looked her id up when she logged in, with a SELECT on her email.
It's a SELECT for the id in the row whose email is ada@simplified.org.
The email is users' logical key, so only Ada's row passes.
And the result holds one row: 1.
The server keeps that 1 for the rest of her visit.
So now it sends the INSERT, with that 1 as the user ID.
Its VALUES are 1, for user ID, and 2, for lesson ID.
user ID is a foreign key, so Postgres looks for a learner with id 1, and finds Ada.
So it stores the attempt as row 8: user ID 1, lesson ID 2.
So that's two queries: one to find the id, at login, and one to add the row.
And the lookup happens once: every answer after that is just an INSERT, with the 1 it kept.
So did the server need that lookup on the day Ada signed up?
No: it got her id from the sign-up itself.
Her sign-up INSERT ended with RETURNING id.
So Postgres sent back her new id, 1, from the same query that made her row.
So the server had her id at once, and kept it.
So her first answer that day took just the INSERT: one query, with no lookup.
Ada's row in users is a parent row: the row on the one side.
Her new attempt is a child row: a row on the many side, holding its parent's id.
So a child row goes in with its parent's id, never its parent's name or email.
So how might Instagram save your comment on a friend's post?
You logged in as sam.codes, and now you comment "So cool!" on post 81.
When you logged in, Instagram's server might have looked up your account's id by your username: 205.
So now it sends an INSERT into comments with post ID 81 and account ID 205.
Your comment holds 205, never sam.codes, so it stays yours if you change your username.
And how might Uber save your trip, without a lookup?
When your Uber account was made, its INSERT ended with RETURNING id, and sent back 512.
Say Uber's server kept that 512.
So when you book a ride to the airport, it sends just one INSERT into trips.
The new trip holds account ID 512, so it shows up in your trips.
So a child row needs its parent's id first: looked up by email at login, or sent back by RETURNING at sign-up.
Simplified's users table holds Dee, with id 4 and the email dee@simplified.org. Dee logs in with her email, so Simplified's server sends SELECT id FROM users WHERE email = 'dee@simplified.org';, and keeps the id that comes back. Then Dee answers a question in lesson 3.
What does the new attempt's user_id hold?
Explanation
First, the lookup at login sends back Dee's id, 4, because only her row has that email.
Then, the server sends the INSERT into attempts with the 4 it kept as user_id, and 3 as lesson_id.
Therefore, the new attempt's user_id is 4.
Eli logs in to Simplified with eli@simplified.org. Simplified's server looks up his id by his email, and keeps it for the rest of his visit. Then Eli answers 3 questions.
How many queries does the server send Postgres in all, for the lookup and his 3 attempts?
Simplified's attempts table has columns id (generated), user_id (a foreign key to users) and lesson_id. Fay types fay@simplified.org on Simplified's login page. Its server needs her id, to put in every attempt she adds on this visit.
Which query does it send?
A new learner, Gus, signs up on Simplified. His INSERT into users ends with RETURNING id, and Postgres sends back 7, which Simplified's server keeps. On the same visit, Gus answers a question in lesson 1.
How many queries does the server send Postgres to add his attempt?
Simplified's attempts table could hold the learner's email, or their id from users. Its user_id is a foreign key to users. Ada's email may change, and her id never changes.
Why does each attempt hold her id, 1, rather than her email?
Simplified's attempts table has columns id (generated), user_id (a foreign key to users) and lesson_id. Ben logs in with ben@simplified.org, so Simplified's server sends SELECT id FROM users WHERE email = 'ben@simplified.org';. The result holds 2, and the server keeps it. Then Ben answers a question in lesson 3.
Which query does the server send for that answer?
Postgres has handed out ids 1 to 6 in Simplified's users table. A new learner, Gia, signs up with an INSERT into users that leaves out id and ends with RETURNING id, and the server keeps the id that comes back. Then Gia answers a question.
What user_id does her attempt hold?