Spotify.cql.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;

CREATE TABLE album_artist (
	-- Album Artist involves Album that has Album ID
	album_id                                ID NOT NULL,
	-- Album Artist involves Artist that has Artist ID
	artist_id                               ID NOT NULL,
	-- Primary index to Album Artist(Album, Artist in "Album is by Artist")
	PRIMARY KEY(album_id, artist_id)
);


CREATE TABLE followed_playlist (
	-- Followed Playlist involves User that has User ID
	user_id                                 ID NOT NULL,
	-- Followed Playlist involves Playlist that has Playlist ID
	playlist_id                             ID NOT NULL,
	-- Primary index to Followed Playlist(User, Playlist in "User follows Playlist")
	PRIMARY KEY(user_id, playlist_id)
);


CREATE TABLE following (
	-- Following involves Follower and User has User ID
	follower_user_id                        ID NOT NULL,
	-- Following involves User that has User ID
	user_id                                 ID NOT NULL,
	-- Primary index to Following(Follower, User in "User follows User")
	PRIMARY KEY(follower_user_id, user_id)
);


CREATE TABLE performance (
	-- Performance involves Song that has Song ID
	song_id                                 ID NOT NULL,
	-- Performance involves Artist that has Artist ID
	artist_id                               ID NOT NULL,
	-- Primary index to Performance(Song, Artist in "Song is by Artist")
	PRIMARY KEY(song_id, artist_id)
);


CREATE TABLE playlist (
	-- Playlist has Playlist ID
	playlist_id                             ID NOT NULL,
	-- Is Collaborative Playlist
	is_collaborative_playlist               BOOLEAN,
	-- Is Secret Playlist
	is_secret_playlist                      BOOLEAN,
	-- maybe Playlist belongs to Artist that has Artist ID
	artist_id                               ID NULL,
	-- maybe Playlist belongs to User that has User ID
	user_id                                 ID NULL,
	-- Primary index to Playlist(Playlist ID in "Playlist has Playlist ID")
	PRIMARY KEY(playlist_id)
);


CREATE TABLE playlist_collaboration (
	-- Playlist Collaboration involves User that has User ID
	user_id                                 ID NOT NULL,
	-- Playlist Collaboration involves Collaborative Playlist that is a kind of Playlist that has Playlist ID
	collaborative_playlist_id               ID NOT NULL,
	-- Primary index to Playlist Collaboration(User, Collaborative Playlist in "User collaborates on Collaborative Playlist")
	PRIMARY KEY(user_id, collaborative_playlist_id),
	FOREIGN KEY (collaborative_playlist_id) REFERENCES playlist (playlist_id)
);


CREATE TABLE playlist_member (
	-- Playlist Member involves Playlist that has Playlist ID
	playlist_id                             ID NOT NULL,
	-- Playlist Member involves Song that has Song ID
	song_id                                 ID NOT NULL,
	-- Is Collaborative Playlist Member
	is_collaborative_playlist_member        BOOLEAN,
	-- maybe Playlist Member is a Collaborative Playlist Member that was added by User that has User ID
	collaborative_playlist_member_user_id   ID NULL,
	-- Primary index to Playlist Member(Playlist, Song in "Playlist contains Song")
	PRIMARY KEY(playlist_id, song_id),
	FOREIGN KEY (playlist_id) REFERENCES playlist (playlist_id)
);


CREATE TABLE playlist_sharing (
	-- Playlist Sharing involves Secret Playlist that is a kind of Playlist that has Playlist ID
	secret_playlist_id                      ID NOT NULL,
	-- Playlist Sharing involves shared_with and User has User ID
	shared_with_user_id                     ID NOT NULL,
	-- Primary index to Playlist Sharing(Secret Playlist, shared_with in "Secret Playlist is shared with User")
	PRIMARY KEY(secret_playlist_id, shared_with_user_id),
	FOREIGN KEY (secret_playlist_id) REFERENCES playlist (playlist_id)
);


CREATE TABLE song (
	-- Song has Song ID
	song_id                                 ID NOT NULL,
	-- maybe Song is on Album that has Album ID
	album_id                                ID NULL,
	-- Primary index to Song(Song ID in "Song has Song ID")
	PRIMARY KEY(song_id)
);


ALTER TABLE followed_playlist
	ADD FOREIGN KEY (playlist_id) REFERENCES playlist (playlist_id);

ALTER TABLE performance
	ADD FOREIGN KEY (song_id) REFERENCES song (song_id);

ALTER TABLE playlist_member
	ADD FOREIGN KEY (song_id) REFERENCES song (song_id);