Database Schema - minuminukim/crates GitHub Wiki

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