SCHEMA

DESIGN

Let's Clone

INSTAGRAM

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 Schema Design

By Colt Steele

MySQL Schema Design

  • 32,980