Schema

Postgres Database Schema

users

column name	data type	details
id	bigint	not null, primary key
username	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 username, unique: true
• index on session_token, unique: true
• has_many videos
• has_many comments
• has_many likes
• has_many dislikes
• has_many subscriptions

videos

column name	data type	details
id	bigint	not null, primary key
title	string	not null
description	text
author_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• author_id references users
• index on author_id
• belongs_to author
• has_many comments
• has_many likes
• has_many dislikes

comments

column name	data type	details
id	bigint	not null, primary key
body	text	not null
user_id	integer	not null, indexed, foreign key
video_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users
• video_id references videos
• index on user_id
• index on video_id
• belongs_to user
• belongs_to video

likes

column name	data type	details
id	bigint	not null, primary key
user_id	integer	not null, indexed, foreign key
video_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users
• video_id references videos
• index on [:user_id, :video_id], unique: true
• belongs_to user
• belongs_to video

dislikes

column name	data type	details
id	bigint	not null, primary key
user_id	integer	not null, indexed, foreign key
video_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• user_id references users
• video_id references videos
• index on [:user_id, :video_id], unique: true
• belongs_to user
• belongs_to video

subscriptions

column name	data type	details
id	bigint	not null, primary key
subscriber_id	integer	not null, indexed, foreign key
subscribed_to_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• subscriber_id references users
• subscribed_to_id references users
• index on [:subscriber_id, :subscribed_to_id], unique: true
• belongs_to subscriber
• belongs_to subscribed_to