Database Schema

users

column name	data type	details
id	integer	not null, primary key
email	string	not null, indexed, unique
first_name	string	not null
last_name	string	not null
user_role	string	not null, in ['student', 'coach']
password_digest	string	not null, indexed, unique
session_token	string	not null, indexed, unique
status	string	null: false, in ['active', 'inactive']
created_at	datetime	not null
updated_at	datetime	not null

index on:

• email
• session_token
• password_digest

profiles

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key, dependent
img_url	string	not null, default image
linkedin_url	string	max 255, unique
github_url	string	max 255, unique
resume_url	string	max 255
cover_letter_url	string	max 255
created_at	datetime	not null
updated_at	datetime	not null

index on:

• user_id

courses

column name	data type	details
id	integer	not null, primary key
title	string	not null, indexed, unique
description	text	not null, limit: 500
img_url	string	default image
duration	integer	in minutes
created_at	datetime
updated_at	datetime

index on:

• title

lessons

column name	data type	details
id	integer	not null, primary key
title	string	not null, indexed, unique
course_id	integer	not null, foreign key
duration	integer	in minutes
lesson_content	text
video_url	string	should contain either text or video
created_at	datetime	not null
updated_at	datetime	not null

index on:

• title
• course_id

tags

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

index on:

• topic

tag_courses (Joins)

column name	data type	details
id	integer	not null, primary key
course_id	integer	not null, indexed
tag_id	integer	not null, indexed

index on:

• course_id
• tag_id
• course_id & tag_id

completed_lesson

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

index on:

• lesson_id
• user_id
• lesson_id & user_id