Schema

Database Schema

---

users

column name	data type	details
id	integer	not null, primary key
email	string	not null, indexed, unique
username	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
display_name	string	not null
biography	text	optional
age	integer	optional
gender	string	optional
country	string	optional
city	string	optional
profile_pic_url	string	optional
cover_pic_url	string	optional
created_at	datetime	not null
updated_at	datetime	not null

• display_name is automatically set to a user's username when making an account, but can later be changed within settings
• display_name is the primary ways a user will be referred to throughout the website
• biography, age, gender, country, city, profile_pic_url, cover_pic_url are optional, and are used to populate a user's public profile visible to other users
• profile_pic_url, cover_pic_url will both have default images if a user does not upload their own images

Associations:

• a user has many tracks
• a user has many comments
• a user has many favorites
• a user has many followers
• a user has many followings

---

followers

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

Associations:

• index on [:user_id, :follower_id], unique: true

---

tracks

column name	data type	details
id	integer	not null, primary key
user_id	integer	not null, foreign key
title	string	not null
audio_url	string	not null
track_length	integer	not null
play_count	integer	not null
description	text	optional
artwork_url	string	optional
background_color	string	optional
created_at	datetime	not null
updated_at	datetime	not null

• track_length is stored in seconds
• artwork_url is automatically set to a user's profile_pic_url if no artwork_url is given
• artwork_url will have a default image if neither artwork_url or a user's profile_pic_url are given

Associations:

• a track belongs to a user
• a track has many comments
• a track has many favorites
• a tag has many tracks through track_tags

---

comments

column name	data type	details
id	integer	not null, primary key
track_id	integer	not null, foreign key
user_id	integer	not null, foreign key
parent_comment_id	integer	optional, foreign key
body	text	not null
created_at	datetime	not null
updated_at	datetime	not null

Associations:

• a comment belongs to a track
• a comment belongs to a user
• a comment belongs to a parent_comment

---

favorites

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

Associations:

• a favorite belongs to a track
• a favorite belongs to a user
• index on [:track_id, :user_id], unique: true`

---

tags

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

associations:

• a tag has many tracks through track_tags

---

track_tags

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

Associations:

• index on [:chirp_id, :user_id], unique: true