Databases · Lesson 1.1
Telling two rows apart
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.
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.
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.
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.
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?
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?
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.
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.
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?
Databases · Lesson 1.2
Who picks the id?
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?
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?
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?
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.
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.
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.
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?
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?
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?
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?
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?
A users table's id is generated by Postgres.
Which of these can happen?
Databases · Lesson 1.3
Why not the email?
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.
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.
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.
So what happens when Ada changes her email?
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.
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?
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?
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.
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.
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?
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?
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?
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?
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?
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?