Schema

Database Schema

users

Column name	datatype	details
id	integer	not null, primary key
username	string	not null, indexed, unique
email	string	indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique

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

shows

Column name	datatype	details
id	integer	not null, primary key
tmdb_id	integer	not null, indexed
title	string	not null, indexed
first_air_date	date	not null, indexed
genre	string	not null, indexed
original_language	string	not null, indexed
poster_path	string	not null, indexed
backdrop_path	string	not null, indexed
overview	string	not null, indexed
rating	integer	indexed
popularity	integer	indexed

• index on tmdb_id
• index on title
• index on first_air_date
• index on rating
• index on genre
• index on original_language
• index on popularity

reviews

Column name	datatype	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
show_id	integer	not null, indexed, foreign key
date_watched	date	indexed
season_progress	string	indexed
body	text	indexed
score	integer	indexed
like	boolean	indexed

• user_id references id in users table
• show_id references id in shows table
• index on date_watched
• index on score
• index on season_progress
• index on body
• index on like
• index on [author_id, show_id], unique: true

lists

Column name	datatype	details
id	integer	not null, primary key
title	string	not null, indexed
creator_id	integer	not null, indexed, foreign key

• creator_id references id in users table
• index on [title, creator_id], unique: true
• index on creator_id

listings

Column name	datatype	details
id	integer	not null, primary key
show_id	integer	not null, foreign key
list_id	integer	not null, foreign key

• show_id references id in shows table
• list_id references id in lists table
• index on [list_id, show_id], unique: true
• index on show_id

watch_statuses

Column name	datatype	details
id	integer	not null, primary key
user_id	integer	not null, foreign key
show_id	integer	not null, foreign key
watched_status	string	not null, indexed

• user_id references id in users table
• show_id references id in shows table
• index on [user_id, show_id], unique: true
• index on show_id
• index on watched_status