Simplified
Simplified

L6.1Where do an app's tables come from?

YOU

Where do an app's tables come from?

usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data
userslessonsattemptscompletions?
ProfileAdaada@simplified.org120 XPCoursesWhat a database isTablesReading dataQuestionWhat a database is✓11users22lessons33attempts44completions
entity
a thing the screens show and the app must remember
usersidemailnamexp1ada@simplified.orgAda1202ben@simplified.orgBen403cy@simplified.orgCy04dee@simplified.orgDee3005eli@simplified.orgEli406fay@simplified.orgFay75
usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data
Profilesam.codeslearning SQLusersidusernamebio205sam.codeslearning SQL206maya.devUI designerPostfirst day!postsiduser_idcaption901205first day!902205day twoCommentmaya.dev: nice!commentsiduser_idpost_idtext3001206901nice!Likesliked by maya.devand sam.codeslikesuser_idpost_id206901206902205901
Channel pageMrBeastchannelsidname1MrBeastVideo pageI Spent 50 Hours Buried AliveMrBeastvideosidchannel_idtitle5011I Spent 50 Hours Buried Alive5021$456,000 Squid Game In Real Life!Commentsinsane!commentsidvideo_idtext8801502insane!
ProfileAdaada@simplified.org120 XPCoursesWhat a database isTablesReading dataQuestionWhat a database is✓11users22lessons33attempts44completions

Databases · Lesson 6.1

Designing Tables

Where do an app's tables come from?

Simplified
0:00 / 2:07

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.

ProfileAdaada@simplified.org120 XPCoursesWhat a database isTablesReading dataQuestionWhat a database is✓11users22lessons33attempts44completions

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?

Profilesam.codeslearning SQLusersidusernamebio205sam.codeslearning SQL206maya.devUI designerPostfirst day!postsiduser_idcaption901205first day!902205day twoCommentmaya.dev: nice!commentsiduser_idpost_idtext3001206901nice!Likesliked by maya.devand sam.codeslikesuser_idpost_id206901206902205901

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?

Channel pageMrBeastchannelsidname1MrBeastVideo pageI Spent 50 Hours Buried AliveMrBeastvideosidchannel_idtitle5011I Spent 50 Hours Buried Alive5021$456,000 Squid Game In Real Life!Commentsinsane!commentsidvideo_idtext8801502insane!

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.

Example: Where do an app's tables come from?

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.

Question 1

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?

One value per cell

usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data
usersidnamelessons_done1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241
SELECT COUNT(*) FROM usersWHERE id = 1;usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—
SELECT COUNT(*) FROM completionsWHERE user_id = 1;completionsuser_idlesson_id111241
usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—
UPDATE users SET lessons_done = '1, 2, 9'WHERE id = 1;usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—lessonsidtitle1What a database is2Tables3Reading data
completionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data
usersidnamelessons_done1Ada1, 2, 92Ben—3Cy—4Dee15Eli—6Fay—lessonsidtitle1What a database is2Tables3Reading data
UPDATE users SET lessons_done = '1, 2, 9'WHERE id = 1;usersidnamelessons_done1Ada1, 2, 92Ben—3Cy—4Dee15Eli—6Fay—UPDATE 1
INSERT INTO completions (user_id, lesson_id)VALUES (1, 9);completionsuser_idlesson_id11124119lessonsidtitle1What a database is2Tables3Reading data
usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—
1NF
first normal form: every cell holds one value, never a list
usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—completionsuser_idlesson_id1112
contactsidnamephones7Sam—resultcount1
phone_numberscontact_idnumber7555-01017555-0199contactsidname7Sam
eventsidnameguests60Team lunch—
eventsidname60Team lunchevent_guestsevent_iduser_id602160226023usersidname21Sam22Maya23Leo
usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—
usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data

Databases · Lesson 6.2

Designing Tables

One value per cell

Simplified
0:00 / 3:05

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.

usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—

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?

SELECT COUNT(*) FROM users WHERE id = 1;usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—resultcount1SELECT COUNT(*) FROM completions WHERE user_id = 1;completionsuser_idlesson_id111241resultcount2

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?

UPDATE users SET lessons_done = '1, 2, 9'WHERE id = 1;usersidnamelessons_done1Ada1, 2, 92Ben—3Cy—4Dee15Eli—6Fay—UPDATE 1lessonsidtitle1What a database is2Tables3Reading dataINSERT INTO completions (user_id, lesson_id)VALUES (1, 9);completionsuser_idlesson_id11124119

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.

usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—completionsuser_idlesson_id111241

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?

phone_numbersidcontact_idnumber112555-0101212555-0199contactsidname11Jo12Sam13Leo

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?

eventsidname60Team lunchevent_guestsevent_iduser_id602160226023usersidname21Sam22Maya23Leo

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.

usersidnamelessons_done1Ada1, 22Ben—3Cy—4Dee15Eli—6Fay—completionsuser_idlesson_id111241

So one row for each value lets the database count the values, and check each id.

Example: One value per cell

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.

Question 1

A table is in first normal form, 1NF.

What does that tell you about its cells?

Question 2

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?

rows

Question 3

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?

Question 4

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?

Question 5

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?

Question 6

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?

Turning a spreadsheet into tables

usersidname1Ada2Ben3Cy4Dee5Eli6Faycompletionsuser_idlesson_id111241lessonsidtitle1What a database is2Tables3Reading data
AB1studentlesson2Jo13Kai14Jo25Kai26Mia17Kai3
AB1studentlesson2Jo13Kai14Jo25Kai26Mia17Kai3usersidname1Ada2Ben3Cy4Dee5Eli6FayJoKaiMia
AB1studentlesson2Jo13Kai14Jo25Kai26Mia17Kai3completionsuser_idlesson_id718172829183usersidname7Jo8Kai9Mia
SELECT users.name, completions.lesson_id FROM completionsJOIN users ON completions.user_id = users.id;completionsuser_idlesson_id718172829183usersidname7Jo8Kai9Mia
normalizing
splitting data into tables linked by keys, so each fact is stored once
completionsuser_idlesson_id718172829183usersidname7Jo8Kai9Mia
AB1videochannel2$456,000 Squid Game In Real Life!MrBeast3I Spent 50 Hours Buried AliveMrBeast4Ages 1 - 100 Fight For $500,000MrBeast5Smartphone AwardsMKBHDchannelsidnameMrBeastMKBHDvideosidtitlechannel_id1$456,000 Squid Game In Real Life!12I Spent 50 Hours Buried Alive13Ages 1 - 100 Fight For $500,00014Smartphone Awards2
AB1emailteam2ivan@gmail.comRocket3zoe@gmail.comRocket4raj@gmail.comRocket5nina@gmail.comPixel6omar@gmail.comPixelteamsidnameRocketPixelsignupsidemailteam_id1ivan@gmail.com12zoe@gmail.com13raj@gmail.com14nina@gmail.com25omar@gmail.com2
completionsuser_idlesson_id718172829183usersidname7Jo8Kai9Mia

Databases · Lesson 6.3

Designing Tables

Turning a spreadsheet into tables

Simplified
0:00 / 3:01

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.

AB1studentlesson2Jo13Kai14Jo25Kai26Mia17Kai3

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.

AB1studentlesson2Jo13Kai14Jo25Kai26Mia17Kai3usersidname7Jo8Kai9Mia

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.

completionsuser_idlesson_id718172829183usersidname7Jo8Kai9Mia

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?

SELECT users.name, completions.lesson_id FROM completionsJOIN users ON completions.user_id = users.id;completionsuser_idlesson_id718172829183usersidname7Jo8Kai9Miaresultnamelesson_idJo1Kai1Jo2Kai2Mia1Kai3

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?

AB1videochannel2$456,000 Squid Game In Real Life!MrBeast3I Spent 50 Hours Buried AliveMrBeast4Ages 1 - 100 Fight For $500,000MrBeast5Smartphone AwardsMKBHDchannelsidname1MrBeast2MKBHDvideosidtitlechannel_id1$456,000 Squid Game In Real Life!12I Spent 50 Hours Buried Alive13Ages 1 - 100 Fight For $500,00014Smartphone Awards2

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?

AB1emailteam2ivan@gmail.comRocket3zoe@gmail.comRocket4raj@gmail.comRocket5nina@gmail.comPixel6omar@gmail.comPixelteamsidname1Rocket2Pixelsignupsidemailteam_id1ivan@gmail.com12zoe@gmail.com13raj@gmail.com14nina@gmail.com25omar@gmail.com2

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.

Example: Turning a spreadsheet into tables

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.

Question 1

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?

rows

Question 2

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?

rows

Question 3

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?

Question 4

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?

Question 5

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?

rows

Question 6

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?

Assistant

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