Simplified
Simplified

L3.1Why one table isn't enough

YOU

Why one table isn't enough

attemptsidlesson_id11223143516271usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay
attemptsidnameemaillesson_id11223143516271usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay
attemptsidnameemaillesson_id1Adaada@simplified.org12Adaada@simplified.org23Benben@simplified.org14Adaada@simplified.org35Deedee@simplified.org16Benben@simplified.org27Deedee@simplified.org1usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay
attemptsiduser_idlesson_id11223143516271usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay
commentsidaccount_idtext501205Love this!502377Nice!503205Same here504205Congrats!accountsidusername205sam.codes377maya.dev
attemptsiduser_idlesson_id111212321413541622741usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

Databases · Lesson 3.1

Two Tables

Why one table isn't enough

Simplified
0:00 / 2:41

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.

attemptsidlesson_id11223143516271usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

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.

attemptsidnameemaillesson_id1Adaada@simplified.org12Adaada@simplified.org23Benben@simplified.org14Adaada@simplified.org35Deedee@simplified.org16Benben@simplified.org27Deedee@simplified.org1

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.

attemptsidnameemaillesson_id1Ada Linada@simplified.org12Ada Linada@simplified.org23Benben@simplified.org14Adaada@simplified.org35Deedee@simplified.org16Benben@simplified.org27Deedee@simplified.org1usersidemailname1ada@simplified.orgAda Lin2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

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.

attemptsiduser_idlesson_id111212321413541622741usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

Now Ada's name is stored in one place: her row in users.

attemptsiduser_idlesson_id111212321413541622741usersidemailname1ada@simplified.orgAda Lin2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

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.

commentsidaccount_idtext501205Love this!502377Nice!503205Same here504205Congrats!accountsidusername205sam.builds377maya.dev

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.

Example: Why one table isn't enough

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.

Question 1

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?

rows

Pointing at a row that exists

INSERT INTO attempts (user_id, lesson_id)VALUES (99, 2);attemptsiduser_idlesson_id1112123214135416227418992usersidname1Ada2Ben3Cy4Dee5Eli6Fay
CREATE TABLE attempts (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id integer NOT NULL,lesson_id integer NOT NULL);
foreign key
a column that holds another table's primary key
attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
CREATE TABLE attempts (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id integer NOT NULLREFERENCESusers (id),lesson_id integer NOT NULL);
INSERT INTO attempts (user_id, lesson_id)VALUES (99, 2);attemptsiduser_idlesson_id111212321413541622741992usersidname1Ada2Ben3Cy4Dee5Eli6Fay
INSERT INTO attempts (user_id, lesson_id)VALUES (4, 2);attemptsiduser_idlesson_id111212321413541622741842usersidname1Ada2Ben3Cy4Dee5Eli6Fay
attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
attemptsiduser_idlesson_id111212321413541622741lessonsidtitle1What a database is2Tables3Reading data
CREATE TABLE attempts (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,user_id integer NOT NULL REFERENCES users (id),lesson_id integer NOT NULLREFERENCES lessons (id));
attemptsiduser_idlesson_id111212321usersidname1Ada2Benlessonsidtitle1What a database is2Tables
INSERT INTO comments (post_id, text)VALUES (81, 'So cool!');commentsidpost_idtext60180Nice desk60281So cool!postsidcaption80New desk81Beach day
attemptsiduser_idlesson_id111212321413541622741992usersidname1Ada2Ben3Cy4Dee5Eli6Fay

Databases · Lesson 3.2

Two Tables

Pointing at a row that exists

Simplified
0:00 / 3:37

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.

INSERT INTO attempts (user_id, lesson_id)VALUES (99, 2);attemptsiduser_idlesson_id1112123214135416227418992usersidname1Ada2Ben3Cy4Dee5Eli6Fay

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.

user_id integer NOT NULL REFERENCES users (id)tablekey

So what does Postgres do with user ID 99 now?

INSERT INTO attempts (user_id, lesson_id)VALUES (99, 2);attemptsiduser_idlesson_id111212321413541622741—992usersidname1Ada2Ben3Cy4Dee5Eli6Fay

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?

INSERT INTO attempts (user_id, lesson_id)VALUES (4, 2);attemptsiduser_idlesson_id111212321413541622741842usersidname1Ada2Ben3Cy4Dee5Eli6Fay

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.

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay

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.

CREATE TABLE attempts (  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  user_id integer NOT NULL REFERENCES users (id),  lesson_id integer NOT NULL REFERENCES lessons (id));attemptsiduser_idlesson_id111212321413541622741lessonsidtitle1What a database is2Tables3Reading data

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.

attemptsiduser_idlesson_id111212321usersidname1Ada2Benlessonsidtitle1What a database is2Tables

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.

INSERT INTO comments (post_id, text)VALUES (81, 'So cool!');commentsidpost_idtext60180Nice desk60281So cool!postsidcaption80New desk81Beach day

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.

Example: Pointing at a row that exists

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.

Question 1

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?

Question 2

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?

Question 3

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?

foreign keys

Question 4

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?

Question 5

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?

rows

Question 6

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?

rows

Which table gets the foreign key?

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
one-to-many
one row goes with many rows in another table, and each of those with just one
attemptsiduser_idlesson_id111212321413541622741manyusersidname1Ada2Ben3Cy4Dee5Eli6Fayone
usersidnameattempt1attempt2attempt3attempt4attempt51Ada2Ben3Cy4Dee5Eli6Fay
attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
INSERT INTO attempts (user_id, lesson_id)VALUES (7, 1);attemptsiduser_idlesson_id11121232141354162274171usersidname1Ada2Ben3Cy4Dee5Eli6Fay7
attemptsiduser_idlesson_id111212321413541622741lessonsidtitle1What a database is2Tables3Reading data
tripsidaccount_iddestination3301512Airport3302640Gym3303512Office3304512Homeaccountsidname512You640Maya
attemptsiduser_idlesson_id111212321413541622741manyusersidname1Ada2Ben3Cy4Dee5Eli6Fayone

Databases · Lesson 3.3

Two Tables

Which table gets the foreign key?

Simplified
0:00 / 2:48

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.

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay

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.

attemptsiduser_idlesson_id111212321413541622741manyusersidname1Ada2Ben3Cy4Dee5Eli6Fayone

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.

usersidnameattempt1attempt2attempt3…1Ada1242Ben36—3Cy———4Dee57—5Eli———6Fay———

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.

INSERT INTO attempts (user_id, lesson_id)VALUES (7, 1);attemptsiduser_idlesson_id111212321413541622741—71usersidname1Ada2Ben3Cy4Dee5Eli6Fay

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?

attemptsiduser_idlesson_id111212321413541622741lessonsidtitle1What a database is2Tables3Reading data

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.

tripsidaccount_iddestination3301512Airport3302640Gym3303512Office3304512Homeaccountsidname512You640Maya

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.

Example: Which table gets the foreign key?

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.

Question 1

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?

Question 2

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?

Question 3

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?

learners

Question 4

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?

rows

Question 5

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?

Question 6

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?

rows

Whose id goes in the new row?

attemptsiduser_idlesson_id111212321413541622741manyusersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFayone
Simplified's serveremail:ada@simplified.orguser_id:1kept from loginattemptsiduser_idlesson_id111212321413541622741?2
SELECT id FROM usersWHERE email = 'ada@simplified.org';usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFayresultid1
Simplified's serveremail: ada@simplified.orguser_id:1kept from loginattemptsiduser_idlesson_id111212321413541622741?2
INSERT INTO attempts (user_id, lesson_id)VALUES (1, 2);attemptsiduser_idlesson_id111212321413541622741812usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay
1SELECT id FROM usersWHERE email = 'ada@simplified.org';once, at login2INSERT INTO attempts (user_id, lesson_id)VALUES (1, 2);for every answer
INSERT INTO users (email, name)VALUES ('ada@simplified.org', 'Ada')RETURNING id;usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFayresultid1
1SELECT id FROM usersWHERE email = 'ada@simplified.org';once, at login1INSERT INTO attempts (user_id, lesson_id)VALUES (1, 1);for every answer
attemptsiduser_idlesson_id111212321413541622741812usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFayparent row
SELECT id FROM accountsWHERE username = 'sam.codes';commentsidpost_idaccount_idtext60180377Nice desk60281205So cool!accountsidusername205sam.codes377maya.dev
INSERT INTO accounts (phone)VALUES ('+1 555 0142')RETURNING id;tripsidaccount_iddestination3304512Home3305512Airportaccountsidname512Youresultid512
attemptsiduser_idlesson_id111212321413541622741812usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

Databases · Lesson 3.4

Two Tables

Whose id goes in the new row?

Simplified
0:00 / 2:59

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.

Simplified's serveremail: ada@simplified.orguser_id:attemptsiduser_idlesson_id111212321413541622741?2

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.

SELECT id FROM users WHERE email = 'ada@simplified.org';usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFayresultid1

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.

INSERT INTO attempts (user_id, lesson_id)VALUES (1, 2);attemptsiduser_idlesson_id111212321413541622741812usersidemailname1ada@simplified.orgAda2ben@simplified.orgBen3cy@simplified.orgCy4dee@simplified.orgDee5eli@simplified.orgEli6fay@simplified.orgFay

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.

1INSERT INTO users (email, name)VALUES ('ada@simplified.org', 'Ada')RETURNING id;resultid12INSERT INTO attempts (user_id, lesson_id) VALUES (1, 1);attemptsiduser_idlesson_id111

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.

attemptsiduser_idlesson_id111212321413541622741812child rowsusersidname1Ada2Ben3Cy4Dee5Eli6Fayparent row

So how might Instagram save your comment on a friend's post?

1SELECT id FROM accountsWHERE username = 'sam.codes';resultid2052INSERT INTO comments (post_id, account_id, text)VALUES (81, 205, 'So cool!');commentsidpost_idaccount_idtext60180377Nice desk60281205So cool!

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?

1INSERT INTO accounts (phone, name)VALUES ('+1 555 0142', 'Sam')RETURNING id;resultid5122INSERT INTO trips (account_id, destination)VALUES (512, 'Airport');tripsidaccount_iddestination3304512Home3305512Airport

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.

Example: Whose id goes in the new row?

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.

Question 1

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?

queries

Question 2

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?

Question 3

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?

queries

Question 4

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?

Question 5

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?

Question 6

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?

Assistant

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