Schema

albums

column name	data type	details
id	integer	not null, primary key
title	string	not null
year	integer	not null
genre	string	not null
artist_id	integer	not null
created_at	datetime	not null
updated_at	datetime	not null

• artist_id references the artists table
• indexing on artist_id

artists

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

follows

polymorphic joins table

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

• user_id references the users table
• indexing on [:user_id, :follow_id], unique: true
• followable_id will reference either the songs, playlists, artists or albums tables based on follow_type
• polymorphic association for followed artists and playlists

playlist_songs

joins table

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

• song_id references the songs table
• playlist_id references the playlists table
• indexing on playlist_id

playlists

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

• user_id references the users table
• indexing on user_id

songs

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

• album_id references the albums table
• indexing on artist_id
• indexing on album_id

user_follows

joins table

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

• follower_id & follower_id reference the users table
• indexing on followee_id
• indexing on [:follower_id, :followee_id], unique: true

users

column name	data type	details
id	integer	not null, primary key
username	string	not null, unique
email	string	not null, unique
session_token	string	not null
password_digest	string	not null
created_at	datetime	not null
updated_at	datetime	not null

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