Databases · Lesson 6.1
Where do an app's tables come from?
Every time a learner finishes a lesson, Simplified's server adds a row to completions.
Ada finished lessons 1 and 2, so she has two rows: 1 and 1, then 1 and 2.
So far, each lesson started from tables already there: users, lessons, attempts and completions.
But a new app starts with no tables at all.
So where do an app's tables come from in the first place?
Mostly from its screens: what they show is what the app must remember.
Your profile shows your name, email and XP, so Simplified must remember each learner: that's users.
Courses shows each lesson's title, so it must remember each lesson: that's lessons.
When you answer a question, it must remember who answered, in which lesson: that's attempts.
And the tick on a finished lesson links a learner to a lesson: that's the join table, completions.
Learners, lessons and attempts are each an entity: a thing the screens show and the app must remember.
Each entity gets a table, and its facts become that table's columns.
And a many-to-many link between two entities gets a join table, like completions.
So how might Instagram's tables come from its screens?
A profile shows a username and a bio, so Instagram might start with a users table.
A post shows its caption, so a posts table, each post holding its user's id.
A comment shows its text under a post, so a comments table, holding the post's id and the commenter's.
You like many posts, and a post gets many likes: that's many-to-many.
So likes get a join table: each row holds a user ID and a post ID.
And YouTube's?
A channel page shows the channel's name and its videos, so YouTube might have a channels table.
A video page shows its title and its channel, so a videos table, each holding its channel's id.
A video page shows the comments under it, so a comments table.
Each comment holds its video's id.
So each thing the screens show and the app must remember gets a table of its own.
Say Spotify's screens show an artist's page, a song with its artist, and a playlist's page listing its songs. A playlist lists many songs, and a song can be in many playlists.
How many tables might Spotify need for these screens?
Explanation
First, the entities are artists, songs and playlists: three tables.
Then, a playlist has many songs and a song is in many playlists, so they need a join table, playlist_songs.
Therefore, Spotify might need 4 tables.
Say Duolingo's screens show a learner's profile, a list of courses, and each course's lessons. A learner takes many courses, and a course has many learners. Each lesson belongs to one course.
Which tables might it need?
Databases · Lesson 6.2
One value per cell
So Simplified's screens gave it four tables, and the tick on a finished lesson is a row of completions.
Ada finished lessons 1 and 2, so she has two rows: 1 and 1, then 1 and 2.
Say a developer on Simplified's team suggests something that looks simpler.
A column on users, lessons done, that holds each learner's list of finished lessons.
Ada's row would hold '1, 2', and Dee's would hold '1'.
That's one row per learner, and no completions table at all.
But then how does Simplified count how many lessons Ada has finished?
To show her progress, the server counts her finished lessons with COUNT star.
But COUNT star counts rows, and Ada's list sits in one row.
So COUNT star on her row in users gives 1, though she finished two lessons.
In completions, each finish is its own row.
So COUNT star on her rows in completions gives 2.
COUNT star can't look inside a cell, so a list in one cell can't be counted with it.
And what stops a lesson that doesn't exist from getting into the list?
Say a bug sets Ada's list to '1, 2, 9', and there is no lesson 9.
A foreign key checks one value against lessons' ids.
But '1, 2, 9' is one piece of text, not an id, so no foreign key can check it.
So the UPDATE works, and lesson 9 sits in Ada's list.
In completions, the row 1 and 9 is refused, because no lesson has id 9.
So a cell should hold one value, never a list.
This rule is called first normal form, or 1NF for short.
And a list becomes rows, one for each value, in a table of its own.
Ada's '1, 2' becomes two rows in completions: 1 and 1, then 1 and 2.
So how might Google Contacts keep a friend's two phone numbers?
Say your friend Sam has a cell number and a home number.
In one cell, they'd be '555-0101, 555-0199', one row that COUNT star counts as 1.
Say Google Contacts keeps them in a phone numbers table instead, one row for each number.
Each row holds Sam's contact id, so Sam has two rows.
And how might Google Calendar keep the guests of an event?
Say your event Team lunch invites three people, with user ids 21, 22 and 23.
In one cell, they'd be '21, 22, 23', and no foreign key could check them.
Say Google Calendar keeps an event guests table instead, one row for each guest of each event.
So Team lunch has three rows, and each user id is checked against the users table.
So that's 1NF: every cell holds one value, never a list.
A list becomes rows in a table of its own, one row for each value.
Then COUNT star can count the values, because each one is a row.
And when the values are ids, a foreign key can check each one.
So one row for each value lets the database count the values, and check each id.
Say Fay's row in users holds lessons_done = '1, 2, 3'. Simplified moves her finished lessons into completions, one row for each lesson.
How many rows in completions replace that cell?
Explanation
First, 1NF means every cell holds one value, so the list becomes one row for each value.
Then, '1, 2, 3' holds three lesson ids.
Therefore, 3 rows replace that cell.
A table is in first normal form, 1NF.
What does that tell you about its cells?
Say Simplified's users table has a lessons_done column. Ada's row holds '1, 2', Dee's holds '1', and Fay's holds '1, 2, 3'. To follow 1NF, Simplified moves every list into completions, one row for each finished lesson.
How many rows go into completions?
Say Simplified's users table has a lessons_done column. Ada's row, id 1, holds '1, 2', and Fay's row, id 6, holds '1, 2, 3'. The server sends SELECT COUNT(*) FROM users WHERE id IN (1, 6);.
What does Postgres send back?
Say Instagram keeps posts in a posts table, with an id and a caption. A post has two photos, a.jpg and b.jpg, and no other post uses them.
Which design follows 1NF?
Say Spotify's playlists table has a text column song_ids, and its songs table has no song with id 999. A bug sets a playlist's song_ids to '14, 27, 999'.
What happens?
Say Simplified's lessons table has four columns: id, title, position, and tags. Lesson 2's row holds 2, 'Tables', 2, and 'schema, keys'.
Which column breaks 1NF?
Databases · Lesson 6.3
Turning a spreadsheet into tables
So each value gets a row of its own: Ada's two finished lessons are two rows of completions.
But data doesn't always arrive in tables.
Say a coding bootcamp moves its students onto Simplified.
It sends a Google Sheet of the lessons they've already finished, one line per finish.
Each line holds the student's name and the lesson's id.
Jo finished lessons 1 and 2, so her name is typed twice.
Kai finished lessons 1, 2 and 3, so his is typed three times. Mia's is typed once.
So if Kai changes his name, all three of his lines must change.
And you risk missing one, leaving Kai with two names.
So how do you turn a sheet like this into tables, with each fact stored once?
First, you find each different student, and give them one row of users.
There are three different students, so users gets three new rows.
Fay has id 6, the last one in users.
So Postgres gives each new row the next id: Jo gets 7, Kai 8, and Mia 9.
Then, you turn each line of the sheet into a row of completions.
Each row keeps the lesson's id, and holds the student's user ID in place of the name.
Jo's first line is lesson 1. Her id in users is 7, so the row holds 7 and 1.
Kai's first line is lesson 1 too. His id is 8, so the row holds 8 and 1.
So the six rows hold the user IDs 7, 8, 7, 8, 9 and 8.
But then how does a screen show each finish with the student's name again?
The server joins completions to users, where completions' user ID equals users' id.
It puts each student's name beside each lesson they finished, just like the sheet.
This is called normalizing.
Normalizing means splitting data into tables linked by keys, so each fact is stored once.
Now Kai's name is stored once, so renaming him changes one row.
So how would you normalize a sheet of your YouTube watch history?
Say each line holds a video's title and its channel.
Four lines: three videos from MrBeast, and one from MKBHD.
So the two channels go into a channels table, one row each: MrBeast gets 1, MKBHD 2.
And each line becomes a row of videos, holding its channel's id: 1, 1, 1 and 2.
And a Google Form's sign-ups for a hackathon?
Say each response holds a person's email and their team's name.
Five people sign up: three on team Rocket, and two on team Pixel.
So the two teams go into a teams table, one row each: Rocket gets 1, Pixel 2.
And each response becomes a row of signups, holding its team's id: 1, 1, 1, 2 and 2.
So normalizing turns a fact typed on many lines into one row, with ids pointing at it.
Say a coding bootcamp moves its students onto Simplified. It sends a Google Sheet of 5,000 finished lessons, one line per finish, with the student's name and the lesson's id. 400 different students appear in it.
How many rows go into the users table?
Explanation
First, each different student gets one row in users, however many lines their name is typed on.
Then, the sheet has 400 different students.
Therefore, 400 rows go into users.
Say a Google Sheet of liked songs has 5,000 lines, one per song, with its title and its artist. 100 different artists appear in it. It's normalized into an artists table and a songs table.
How many rows go into songs?
Say a Google Sheet of your YouTube watch history has 2,000 lines, one per video, with its title and its channel. 40 different channels appear in it, and MrBeast is on 300 lines. It's normalized into a channels table and a videos table.
How many rows of videos hold MrBeast's channel id?
Say a Google Sheet of liked songs lists each song's title and artist. It's normalized into an artists table and a songs table.
What does each row of songs hold in place of the artist's name?
Say a hackathon's Google Form sign-ups each hold an email and a team name. They're normalized into a teams table and a signups table. Team Rocket has 30 sign-ups.
Why does Rocket get just one row in teams?
Say a Google Sheet listed 300 songs by one artist, each line typing the artist's name. It's normalized into an artists table and a songs table. Then the artist changes their name.
How many rows must change?
Say a sheet of YouTube watch history lists each video's title and channel. It's been normalized into a channels table and a videos table, where each video holds channel_id. A screen wants to list each video with its channel's name.
How does it get them?