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
birthday	date	not null
password_digest	string	not null
session_token	string	not null, indexed, unique
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
• has_many playlists (future association to playlists when users can be deleted)
• has_many queued songs (in the future, the queue would be persisted to the database to be resumed)
• has_many liked_songs (stretch feature), referencing songs through likings

songs

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

• duration is song length in seconds
• album_id references albums
• index on title
• index on :album_id, :title, unique:true
• belongs_to album
• has_one song_artist referencing artists through album
• has_many collab_artists referencing artists through collab_artist
• has_many playlisteds referencing playlisted
• has_many playlists referencing playlist through playlisted
• has_one like (bonus/stretch feature)

albums

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

• artist_id references artists
• index on title
• index on [:artist_id, :title], unique: true
• index on photo_url
• belongs_to album_artist, referencing artists
• has_many tracks, referencing songs

artists

Column Name	Data Type	Details
id	integer	not null, primary key
name	string	not null, indexed
photo_url	string	indexed
num_listeners	string	indexed
created_at	datetime	not null
updated_at	datetime	not null

• index on name
• index on photo_url
• has_many albums, referencing albums
• has_many collabs, referencing collabs
• has_many collab_songs, referencing songs through collabs

playlists

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

• user_id references users
• index on user_id
• index on updated_at
• belongs_to user future association to users when users can be deleted
• has_many playlistedreferencingplaylisted`, which are destroy-dependent
• has_many playlist_songs referencing songs through playlisted

collabs

This is a joins table to allow multiple collaboration artists to be associated with a song.

column name	data type	details
song_id	integer	not null, foreign key
artist_id	integer	not null, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• song_id references songs
• artist_id references artists
• index on [:song_id, :artist_id], unique: true
• belongs_to collab referencing songs
• belongs_to collab_artist referencing artists

playlisteds

This is a joins table to allow songs to be added to many playlists multiple times.

Column Name	Data Type	Details
id	integer	not null, primary key
song_id	integer	not null, indexed, foreign key
playlist_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• song_id references songs
• playlist_id references playlists
• index on playlist_id
• index on song_id
• belongs_to song
• belongs_to playlist

liked songs (bonus/stretch feature)

This would be a joins table allowing songs to be liked (once) by multiple users.

Column Name	Data Type	Details
id	integer	not null, primary key
song_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

• song_id references songs
• user_id references users
• index on [:song_id, :user_id], unique: true
• belongs_to song
• belongs_to user

queued songs (bonus/stretch feature)

This would be a joins table allowing songs to be saved in the queues of multiple users.

column name	data type	details
id	integer	not null, primary key
song_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

• song_id references songs
• user_id references users
• index on song_id
• index on user_id
• belongs_to song
• belongs_to user