database schema

users

column_name	data_type	details
id	integer	not null, primary key
username	string	not null, unique, indexed
email	string	not null, unique
password_digest	string	not null
session_token	string	not null, unique, indexed
date_of_birth	date	not null

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

games

column_name	data_type	details
id	integer	not null, unique, indexed, primary key
title	string	not null, unique

videos

column_name	data_type	details
id	integer	not null, unique, indexed, primary key
title	string	not null
streamer_id	integer	not null, indexed

• index on streamer_id
• streamer_id is a foreign_key which references user

follows

column_name	data_type	details
id	integer	not null, unique, indexed, primary key
follower_id	integer	not null
streamer_id	integer	not null

• follower_id and streamer_id will both be foreign_keys which refer to users

stream

column_name	data_type	details
id	integer	not null, unique, indexed, primary key
streamer_id	integer	not null
game_id	integer	not null

• game_id is a foreign_key that will indicate which game that stream belongs to
• streamer_id is also a foreign_key that will indicate a belongs to association with users

views

column_name	data_type	details
id	integer	not null, primary key
viewer_id	integer	not null
stream_id	integer	not null, indexed

• views will track users who are viewing a stream by joining the two tables
• in order to quickly display and update the current_viewers of a stream and, at a higher level, a game, we will want to index on stream_id
• foreign_keys for this table will be both viewer_id and stream_id