Schema

Postgres Database Schema

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, indexed, unique
created_at	datetime	not null
updated_at	datetime	not null

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

movies

column name	data type	details
id	integer	not null, primary key
title	string	not null, indexed
description	string	not null
year	integer	not null
rating	string	not null
runtime	string	not null
url	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on title

genre

column name	data type	details
id	integer	not null, primary key
name	string	not null

moviegenre

column name	data type	details
id	integer	not null, primary key
genre_id	integer	not null, indexed, foreign key
movie_id	integer	not null, indexed, foreign key

• index on movie_id
• movie_id references movies
• index on genre_id
• genre_id references genres

lists

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, indexed, foreign key
movie_id	integer	not null, indexed, foreign key

• user_id references user
• movie_id references movies
• index on [:user_id, :movie_id], unique: true // We don't need a separate index for user_id or movie_id because the first index adds it for us.

If time permits:

castings

column name	data type	details
id	integer	not null, primary key
movie_id	integer	not null, indexed, foreign key
people_id	integer	not null, indexed, foreign key
role	string	not null

• index on movie_id
• movie_id references movies
• index on people_id
• people_id references people

people

column name	data type	details
id	integer	not null, primary key
name	string	not null