Database Schema

Users

Column Name	Data Type	Details
id	int	primary key, not null
username	varchar(30)	not null, unique
email	varchar(255)	not null, unique
hashedPassword	varbinary	not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

Albums

Column Name	Data Type	Details
id	int	primary key, not null
spotifyID	varchar(255)	unique
title	varchar(255)	not null
averageRating	float(3, 1)	not null, default = 0.0, max = 10.0
ratingsCount	int	not null, default = 0
artist	varchar(255)	not null
artworkURL	text	not null
releaseYear	int	not null
genres	text array	default = []
createdAt	timestamp	not null
updatedAt	timestamp	not null

• spotifyID is a base-62 identifier returned via the Spotify API

Reviews

Column Name	Data Type	Details
id	int	primary key, not null
userID	int	foreign key, not null
albumID	int	foreign key, not null
body	varchar(4000)
listenedDate	dateonly	not null
rating	int	not null, min = 1, max = 10
isRelisten	boolean	not null, default = false
createdAt	timestamp	not null
updatedAt	timestamp	not null

• userID references Users.id
• albumID references Albums.id

Lists

Column Name	Data Type	Details
id	int	primary key, not null
userID	int	foreign key, not null
title	varchar(100)	not null
description	varchar(4000)
isRanked	boolean	not null, default = false
createdAt	timestamp	not null
updatedAt	timestamp	not null

• userID references Users.id

AlbumLists

Column Name	Data Type	Details
id	int	primary key, not null
albumID	int	foreign key, not null
listID	int	foreign key, not null
listIndex	int
createdAt	timestamp	not null
updatedAt	timestamp	not null

• albumID references Albums.id
• listID references Lists.id

Backlogs

Column Name	Data Type	Details
id	int	primary key, not null
userID	int	foreign key, not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

• userID references Users.id

AlbumBacklogs

Column Name	Data Type	Details
id	int	primary key, not null
albumID	int	foreign key, not null
backlogID	int	foreign key, not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

• albumID references Albums.id
• backlogID references Backlogs.id

Artists

Column Name	Data Type	Details
id	int	primary key, not null
spotifyID	varchar(255)	unique
name	varchar(255)	not null
imageURL	text	not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

• currently not implemented

• spotifyID is a base-62 identifier returned via the Spotify API

Tracks

Column Name	Data Type	Details
id	int	primary key, not null
spotifyID	varchar(255)	unique
albumID	int	foreign key, not null
title	varchar(255)	not null
durationMS	int	not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

• currently not implemented

• albumID references Albums.id
• spotifyID is a base-62 identifier returned via the Spotify API

UserAlbums

Column Name	Data Type	Details
id	int	primary key, not null
userID	int	foreign key, not null
albumID	int	foreign key, not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

• join table to track albums a user has marked as 'listened'
• userID references Users.id
• albumID references Albums.id

Comments

Column Name	Data Type	Details
id	int	primary key, not null
userID	int	foreign key, not null
reviewID	int	foreign key, not null
body	varchar(500)	not null
createdAt	timestamp	not null
updatedAt	timestamp	not null

• userID references Users.id
• reviewID references Reviews.id