schema

Postgres Database Schema

users

column name	data type	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
is_instructor	boolean	not null
created_at	datetime	not null
updated_at	datetime	not null

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

workout_classes

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed, unique
date	datetime	not null
skill_level	string	not null
instructor_id	integer	not null, indexed, foreign key
category_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• index on name, unique: true
• index on instructor_id
• index on category_id
• as of now a class has_one instructor

categories

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

• index on name, unique: true

users_workout_classes

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

• user_id references users
• class_id references classes

user_follows

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

• user_id references users
• followed_user_id references users
• index on [:user_id, :followed_user_id], unique: true // this pairing will allow a user to only follow another user once

challenges

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed
start_date	datetime	not null
end_date	datetime	not null
active	boolean	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on name, unique: true

user_challenges

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

• user_id references users
• challenge_id references challenges
• index on [:user_id, :challenge_id], unique: true

songs

column name	data type	details
id	integer	not null, primary key
name	string	not null
artist	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• index on :name
• index on artist

workout_class_songs

column name	data type	details
id	integer	not null, primary key
song_id	integer	not null, indexed, foreign key
workout_class_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• song_id references songs
• workout_class_id references workout_classes