Database Schema

Schema Diagram

users

column name	data type	details
id	integer	not null, primary key
email	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null
created_at	datetime	not null
modified_at	datetime	not null

• index on username, unique: true
• index on email, unique: true
• index on session_token, unique: true

profiles

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed
name	string	not null
image_url	string	not null
created_at	datetime	not null
modified_at	datetime	not null

• index on user_id
• index on [user_id, name], unique: true

mylist_videos

column name	data type	details
id	integer	not null, primary key
profile_id	integer	not null, indexed
video_id	integer	not null, indexed
created_at	datetime	not null
modified_at	datetime	not null

• index on profile_id
• index on video_id

watched_videos

column name	data type	details
profile_id	integer	not null, indexed
video_id	integer	not null, indexed
last_watched_time	integer	not null
created_at	datatime	not null
modified_at	datetime	not null

• index on profile_id
• index on video_id
• last_watched_time is in seconds

videos

column name	data type	details
id	integer	not null, primary key
video_url	string	not null, unique
show_id	integer	not null, indexed
episode_num	integer
name	string	not null
description	string	not null
type	string	not null
runtime	integer	not null
credits_time	integer
created_at	datetime	not null
modified_at	datetime	not null

• index on show_id
• type is either PREVIEW, FILM, or EPISODE

shows

column name	data type	details
id	integer	not null, primary key
poster_url	string	not null
title	string	not null
director	string	not null
tagline	string	not null
year	integer
maturity rating	string
view_count	integer	not null
type	string	not null
created_at	datetime	not null
modified_at	datetime	not null

• type is either FEATURE or EPISODIC

likes

column name	data type	details
id	integer	not null, primary key
profile_id	integer	not null, indexed
show_id	integer	not null, indexed
rating	integer	not null
created_at	datetime	not null
modified_at	datetime	not null

• index on profile_id
• index on show_id
• index on [profile_id, show_id], unique: true

show_genres

column name	data type	details
id	integer	not null, primary key
show_id	integer	not null, indexed
genre_id	integer	not null, indexed
created_at	datetime	not null
modified_at	datetime	not null

• index on movie_id
• index on genre_id

genre

column name	data type	details
id	integer	not null, primary key
name	string	not null, unique
created_at	datetime	not null
modified_at	datetime	not null

• name is unique: true

recommened_genres

column name	data type	details
id	integer	not null, primary key
genre_id	integer	not null, indexed
profile_id	integer	not null, indexed
num_views	integer	not null
created_at	datetime	not null
modified_at	datetime	not null

• index on genre_id
• index on profile_id