Simplified
Simplified

L1.1Telling two rows apart

YOU

Telling two rows apart

queryadd 10 XP to Benusersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18?
Ben@ben.kimBen@benlopez
usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18↑ primary key
primary key
a column whose value is different in every row, and never empty
queryadd 10 XP to thelearner with id 2usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18↑ primary key
your receiptorder number4466phone case $24.99ordersorder numberitemdatetotal4466phone caseOct 3$24.994469phone caseOct 3$24.99↑ primary key
queryopen playlist 501playlistsplaylist idnameowner501Gymyou502Gymyour friend↑ primary key
queryadd 10 XP to thelearner with id 2usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18↑ primary key

Databases · Lesson 1.1

Keys

Telling two rows apart

Simplified
0:00 / 2:42

Simplified, the app you're learning on, keeps its learners in a users table, in Postgres.

Each learner got one row when they signed up, with their email, name and XP.

Say a second Ben signs up, with the email ben.k@simplified.org.

So now two rows have Ben in the name column.

The first Ben has 40 XP, and the new Ben has 0 so far.

Then the first Ben finishes a lesson worth 10 XP.

So Simplified's server sends Postgres a query: add 10 XP to Ben.

queryadd 10 XP to Benusersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18?

Both rows say Ben, so Postgres would add 10 XP to both of them.

And the new Ben would get XP he never earned.

But how can the query point at just the first Ben?

Well, you've seen the same problem on Instagram.

Two accounts can both show the name Ben.

Each one has its own username, though, and no two accounts share one.

So Instagram can always tell the two Bens apart.

Ben@ben.kimBen@benlopez

The users table has a column like that: id.

It holds the number Simplified gives each learner.

The first Ben's id is 2, and the new Ben's id is 7.

No other row has 2, and no other row has 7.

usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18↑ primary key

This is called a primary key.

A primary key is a column whose value is different in every row, and never empty.

A table has one primary key. In the users table, it's id.

queryadd 10 XP to thelearner with id 2usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen40502026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18↑ primary key

So the server's query now says: add 10 XP to the learner with id 2.

Postgres finds the one row whose id is 2.

That row's XP goes from 40 to 50.

The new Ben's row, id 7, still has 0 XP.

Now how does Amazon tell two of your orders apart?

your receiptorder number4466phone case $24.99ordersorder numberitemdatetotal4466phone caseOct 3$24.994469phone caseOct 3$24.99↑ primary key

Say you order the same phone case twice on the same day, for $24.99 each.

The two orders have the same item, the same date and the same total.

But each one has its own order number, printed on its receipt.

Say Amazon keeps its orders in a table, with the order number as its primary key.

So when you return one case, its order number points at exactly one row.

And how does Spotify open your Gym playlist, and not your friend's?

queryopen playlist 501playlistsplaylist idnameowner501Gymyou502Gymyour friend↑ primary key

You and your friend each make a playlist called Gym.

Say Spotify keeps playlists in a table, with a playlist id as its primary key.

So each Gym playlist gets its own id, say 501 for yours and 502 for your friend's.

When you open yours, Spotify asks for playlist 501.

So your songs play, not your friend's.

So two learners can share a name, but never an id.

And the primary key points a query at exactly one row.

Example: Telling two rows apart

Simplified's users table has two learners named Ben. One has id 2 and 40 XP. The other has id 7 and 0 XP. The Ben with id 7 finishes a lesson worth 12 XP.

Which value should the server's query use to pick out that Ben?

Explanation

First, both rows have Ben in the name column, so the name points at both of them.

Then, the primary key, id, is different in every row, and this Ben's id is 7.

Therefore, the query should use his id, 7.

Question 1

A users table has 50 learners, and three of them are named Dee, with ids 4, 19 and 33. The server sends the database a query: add 5 XP to the learner named Dee.

In how many rows does the XP change?

rows

Who picks the id?

sign upemailben.k@simplified.orgnameBenusersidname1Ada2Ben3Cy4Dee5Eli6Fay7BenPostgresnext number
generated
Postgres hands out the next number itself: 1, 2, 3, and so on
ordersorder numberwhose order4470a shopper4471a shopper4472you4473the next shopperAmazon's database
usersidname1Ada2Ben3Cy4Dee5Eli6Fay7Ben8GusPostgresnext number
usersidname2Ben
usersidname1Ada2Ben3Cy4Dee5Eli6Fay7Ben8GusPostgresnext number

Databases · Lesson 1.2

Keys

Who picks the id?

Simplified
0:00 / 1:44

So the query told the two Bens apart by their ids, 2 and 7.

The new Ben got his id, 7, the moment he signed up.

But he never typed a number on the sign-up form.

So who picked the new Ben's id, 7?

sign upemailben.k@simplified.orgnameBenusersidname1Ada2Ben3Cy4Dee5Eli6Fay7BenPostgresnext number

Not the new Ben. He typed only his email and his name.

It's like your Amazon order number: you never pick it.

Postgres handed him one itself, the next number after 6.

This is called a generated id.

Generated means Postgres hands out the next number itself: 1, 2, 3, and so on.

Now how does your Amazon order get its number?

ordersorder numberwhose order4470a shopper4471a shopper4472you4473the next shopperAmazon's database

Say Amazon keeps its orders in a table, with the order number as its primary key.

You place an order, and you never type its number.

Say the last order number Amazon's database handed out was 4471.

So it hands your order the next one, 4472, and that's on your receipt.

Then the next shopper's order gets 4473.

So what happens to an id when its learner leaves?

usersidname1Ada2Ben3Cy4Dee5Eli6Fay7Ben8GusPostgresnext number

Say Fay, whose id is 6, deletes her account.

Her row is gone from the users table.

Then a new learner, Gus, signs up.

Postgres gives him 8, the next number after 7, not Fay's old 6.

Because Postgres never hands out the same id twice.

That way, id 6 only ever meant Fay, so no one is mistaken for her.

And a row's id never changes.

Say the first Ben changes his name to Benji. His row is still id 2.

usersidname2BenBenji

So Postgres hands out each id once, and never hands it out again.

And a row keeps its id for good, so that id always means that one learner.

Example: Who picks the id?

Postgres has handed out the ids 1 to 7 in Simplified's users table. Then the learner with id 7 deletes their account. Then a new learner signs up.

Which id does Postgres give the new learner?

Explanation

First, the last id Postgres handed out was 7.

Then, Postgres never hands out the same id twice, so 7 isn't given again.

Therefore, the new learner gets id 8.

Question 1

Say Duolingo keeps its learners in Postgres, in a table whose id is generated. You sign up with your email and your name.

Who picks your id?

Question 2

A users table's ids are generated, starting at 1. So far, Postgres has handed out the ids 1 to 40. Then 3 learners delete their accounts. Then 2 new learners sign up.

What id does the second of those new learners get?

Question 3

Say Duolingo keeps its learners in Postgres, with a generated id starting at 1. So far, 500 learners have signed up, and none has deleted their account.

What id does the next learner get?

Question 4

In a users table with a generated id, Cy's row has id 3. Cy changes his name to Cyrus. Then 10 new learners sign up.

What is Cy's id now?

Question 5

A users table has a generated id. Fay, whose id is 6, deletes her account. Say Postgres then gave 6 to the next learner, Gus.

What would that risk?

Question 6

A users table's id is generated by Postgres.

Which of these can happen?

Why not the email?

usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18?
your Instagram accountemailyou@gmail.composts48followers210DMs35
Ada logs inada@simplified.orgusersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-09↑ primary key↑ logical key
logical key
the value people use to find a row, like the email you log in with
Ada logs inada@simplified.orgusersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-09↑ primary key↑ logical key
querychange the email ofthe learner with id 1usersidemailnamexp1ada@simplified.orgAda120
usersidemailname1ada.codes@gmail.comAdafinished lessonslearner idlessonxp1Where your photo lives101Kinds of database101Tables10………
your Instagram accountusername@sam.codesposts48followers210
your Spotify accountemailyou@gmail.complaylists12
Ada logs inada@simplified.orgusersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-09↑ primary key↑ logical key

Databases · Lesson 1.3

Keys

Why not the email?

Simplified
0:00 / 2:51

So the query picked out the first Ben by his id, 2, which Postgres handed out.

But the two Bens' emails are different too: ben@simplified.org and ben.k@simplified.org.

In fact, no two learners share an email, and each one logs in with theirs.

usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ben.k@simplified.orgBen02026-04-18?

So why give each row an id at all, instead of using the email?

Well, think about the email on your Instagram account.

You can change it in your settings, any time.

Your posts, your followers and your DMs all stay.

It's still your account, just with a new email.

your Instagram accountemailyou@gmail.com→you@outlook.composts48followers210DMs35

So an email can change. But a row's id never does.

Ada logs in to Simplified with ada@simplified.org.

So Simplified's server asks Postgres for the row with that email, and gets hers.

The email is how people find a row from the outside.

This is called a logical key.

A logical key is the value people use to find a row, like the email you log in with.

A logical key can change, so the primary key is the id, which never does.

Ada logs inada@simplified.orgusersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-09↑ primary key↑ logical key

So what happens when Ada changes her email?

querychange the email ofthe learner with id 1usersidemailnamexp1ada@simplified.orgada.codes@gmail.comAda120

Say Ada switches to a new email, ada.codes@gmail.com.

Simplified's server sends Postgres a query: change the email of the learner with id 1.

Postgres finds the one row whose id is 1.

Only its email cell changes. Her id is still 1, and her XP is still 120.

But what about the tables that point at Ada's row?

Tables link to each other with ids, like a call holding the id of the contact you called.

Say Simplified keeps a finished lessons table too.

Each row is one lesson a learner finished, and it holds that learner's id.

usersidemailname1ada.codes@gmail.comAdafinished lessonslearner idlessonxp1Where your photo lives101Kinds of database101Tables10………

Ada has finished 12 lessons, each worth 10 XP, so 12 rows hold her id, 1.

After her email change, they still hold 1, so they still point at her.

If they held her email instead, all 12 rows would have to change too.

Now what happens when you change your Instagram username?

your Instagram accountusername@sam.codes→@sam.buildsposts48followers210

People search your username to find you, so it's a logical key too.

Say you change yours from @sam.codes to @sam.builds.

Instagram might keep an id for your account that never changes.

Your posts and followers would point at that id, so they all stay.

Only the username changes.

And what happens when you change the email on your Spotify account?

your Spotify accountemailyou@gmail.com→you@outlook.complaylists12

You log in to Spotify with your email, so it's a logical key too.

Say you switch to a new email.

Spotify might keep your playlists under your account's id, which never changes.

So all 12 of your playlists are still yours, under your new email.

So the id is how the database names a row, and it never changes.

And the email is how people find that row, and it can change.

Example: Why not the email?

Dee's row in Simplified's users table has id 4 and the email dee@simplified.org. She has finished 30 lessons, and each of those rows in the finished lessons table holds her id. Then she changes her email to dee.writes@gmail.com.

Which value do her 30 finished lessons rows still hold?

Explanation

First, those rows point at Dee with her id, 4, not with her email.

Then, changing her email changes one cell in the users table, and her id never changes.

Therefore, they still hold her id, 4.

Question 1

Say Spotify keeps its accounts in a table with four columns: an id that Postgres generates, an email, a display name and a country. You log in to Spotify with your email.

Which column is the logical key?

Question 2

An online bookstore keeps its customers in a table with a generated id and an email. No two customers share an email, and each one logs in with theirs.

Why is the id the primary key, and not the email?

Question 3

Leo's row in a users table has id 23. A finished lessons table has 15 rows that hold Leo's id, 23. Then Leo changes his email.

How many cells change in all, across both tables?

cells

Question 4

Say Uber keeps its riders in a table with a generated id, an email and a name. Its trips table needs to say which rider took each trip.

Which value should each trip's row hold?

Question 5

On Instagram, people search your username to find your account, and you can change it any time. Say Instagram also keeps an id for every account that never changes.

Which of these is a logical key for your account?

Question 6

Say an online bookstore's orders table held each customer's email, instead of their id. Kai has placed 8 orders. Then he changes his email.

How many cells must change in all, counting the email in his own row of the customers table?

cells
Assistant

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