Schema

Database Schema

users

Column name	datatype	details
id	integer	not null, primary key
username	string	not null, indexed, unique
email	string	not null, 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
title	string	not null, indexed
year	integer	not null, indexed
genre	string	not null, indexed
rating	integer	indexed
service	string	not null, indexed

• index on title
• index on year
• index on rating
• index on genre
• index on service

reviews

Column name	datatype	details
id	integer	not null, primary key
title	string	not null
body	text	not null
score	integer	indexed
author_id	integer	not null, indexed, foreign key
show_id	integer	not null, indexed, foreign key

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

lists

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

• creator_id references id in users table
• index on [name, 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

shows_users

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