SCHEMA
DESIGN
Let's Clone
USERS
PHOTOS
COMMENTS
LIKES
HASHTAGS
FOLLOWERS/FOLLOWEES
GIVE IT A SHOT
WRITE A SCHEMA
| USERS |
|---|
| id |
| username |
| created_at |
Let's Start With Users...
| USERS |
|---|
| id |
| username |
| created_at |
Up Next: Photos
| PHOTOS |
|---|
| id |
| image_url |
| user_id |
| created_at |
| USERS |
|---|
| id |
| username |
| created_at |
Now Comments
| PHOTOS |
|---|
| id |
| image_url |
| user_id |
| created_at |
| COMMENTS |
|---|
| id |
| comment_text |
| user_id |
| photo_id |
| created_at |
| USERS |
|---|
| id |
| username |
| created_at |
What About Likes?
| PHOTOS |
|---|
| id |
| image_url |
| user_id |
| created_at |
| LIKES |
|---|
| user_id |
| photo_id |
| created_at |
| USERS |
|---|
| id |
| username |
| created_at |
Relationship Troubles
| FOLLOWS |
|---|
| follower_id |
| followee_id |
| created_at |
| ID | USERNAME | CREATED_AT |
|---|---|---|
| 1 | Tommy | 2016-10-11 |
| 2 | BlueCat | 2017-01-10 |
| 3 | ColtSteele | 2017-04-04 |
EXAMPLE TIME
| FOLLOWER_ID | FOLLOWEE_ID | CREATED_AT |
|---|---|---|
| 3 | 2 | 2017-03-03 |
| 3 | 1 | 2017-04-04 |
| 2 | 3 | 2017-05-05 |
| FOLLOWER_ID | FOLLOWEE_ID | CREATED_AT |
|---|---|---|
| 3 | 2 | 2017-03-03 |
| 3 | 1 | 2017-04-04 |
| FOLLOWER_ID | FOLLOWEE_ID | CREATED_AT |
|---|---|---|
| 3 | 2 | 2017-03-03 |
Users
Follows
| FOLLOWER_ID | FOLLOWEE_ID | CREATED_AT |
|---|
AND NOW...Tags!
(The Fun One)
There are 3 popular solutions to tagging
| id | image_url | caption | tags |
|---|---|---|---|
| 1 | '/ksjd97123' | 'My cat' | '#cat#pets#animals#cute#omg' |
| 2 | '/098fsdskj' | 'My meal' | '#microwave#sadfood#gross' |
| 3 | '/87hghjkd' | 'A Selfie' | '#smile#ego#cute#srrynotsrry' |
Solution 1
Super easy to implement
Limited number of tags can be stored
Have to be careful with searching
Cannot store additional information
| id | image_url | caption |
|---|---|---|
| 1 | '/ksjd97123' | 'My cat' |
| 2 | '/098fsdskj' | 'My meal' |
| 3 | '/87hghjkd' | 'A Selfie' |
Solution 2
Unlimited Number Of Tags
Slower Than Previous Solution
| tag_name | photo_id |
|---|---|
| '#cute' | 1 |
| '#cute' | 3 |
| '#microwave' | 2 |
| '#ego' | 3 |
| '#smile | 3 |
| '#gross' | 2 |
Tags
Photos
AND FINALLY...
SOLUTION 3
| id | image_url | caption |
|---|---|---|
| 1 | '/ksjd97123' | 'My cat' |
| 2 | '/098fsdskj' | 'My meal' |
| 3 | '/87hghjkd' | 'A Selfie' |
More work when inserting/updating
| id | tag_name |
|---|---|
| 1 | '#cute' |
| 2 | '#pets' |
| 3 | '#microwave' |
| 4 | '#ego' |
| 5 | '#smile |
| 6 | '#gross' |
Tags
Photos
Photo_Tags
Unlimited Number Of Tags
Have to worry about orphans
| photo_id | tag_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 6 |
| 3 | 1 |
| 3 | 4 |
| 3 | 5 |
Can add additional information
YOUR
TURN
Step 1
INSERT a ton of data
Step 2
Ask Some Questions
We want to reward our users who have been around the longest.
Find the 5 oldest users.
What day of the week do most users register on?
We need to figure out when to schedule an ad campgain
We want to target our inactive users with an email campaign.
Find the users who have never posted a photo
We're running a new contest to see who can get the most likes on a single photo.
WHO WON??!!
Our Investors want to know...
How many times does the average user post?
A brand wants to know which hashtags to use in a post
What are the top 5 most commonly used hashtags?
We have a small problem with bots on our site...
Find users who have liked every single photo on the site
We also have a problem with celebrities
Find users who have never commented on a photo
Are we overrun with bots and celebrity accounts?
Find the percentage of our users who have either never commented on a photo or have commented on every photo
MEGA CHALLENGE
MySQL 2.0: Schema Design
By Colt Steele
