Spotify Database

Overview

Three tables. Users, songs, and listens. Ratings and timestamps track user preferences.

Users Table (4 rows)

user_id	name	email	Notes
1	Mickey	mickey@example.com	Blue bars for user_id=1 in tables
2	Minnie	minnie@example.com	Orange bars for user_id=2 in tables
3	Daffy	daffy@example.com	Purple bars for user_id=3 in tables
4	Pluto	pluto@example.com	Cyan bars for user_id=4 in tables

Keys: user_id (PRIMARY KEY)

PRIMARY KEY: The unique identifier for each row—no duplicates, no NULLs.

Email is UNIQUE too, but IDs are the go-to choice. Emails change; IDs don't.
Composite primary keys: Used when multiple columns together uniquely identify a row, like (sunid, class_id) in a Grades table.

Songs Table (10 rows)

song_id	title	artist	genre
1	Evermore	Taylor Swift	Pop
2	Willow	Taylor Swift	Pop
3	Shape of You	Ed Sheeran	Rock
4	Photograph	Ed Sheeran	Rock
5	Shivers	Ed Sheeran	Rock
6	Yesterday	Beatles	Classic
7	Yellow Submarine	Beatles	Classic
8	Hey Jude	Beatles	Classic
9	Bad Blood	Taylor Swift	Rock
10	DJ Mix	DJ	NULL

Primary Keys: song_id

Listens Table (9 rows)

listen_id	user_id	song_id	rating	listen_time
1	1	1	4.5	2024-08-30 14:35:00
2	1	2	4.2	NULL
3	1	6	3.9	2024-08-29 10:15:00
4	2	2	4.7	NULL
5	2	7	4.6	2024-08-28 09:20:00
6	2	8	3.9	2024-08-27 16:45:00
7	3	1	2.9	NULL
8	3	2	4.9	2024-08-26 12:30:00
9	3	6	NULL	NULL

Primary Keys: listen_id

Foreign Keys: Listens.user_id → Users.user_id, Listens.song_id → Songs.song_id

FOREIGN KEY: This links to another table's PRIMARY KEY, ensuring you can't reference users or songs that don't exist.

Database Schema (CREATE TABLE Statements)

Learn to read SQL DDL (Data Definition Language) that creates these tables:

Users Table

CREATE TABLE users (
  user_id    INT           PRIMARY KEY,     -- Unique ID for each user
  name       VARCHAR(100)  NOT NULL,        -- User's name (required)
  email      VARCHAR(255)  UNIQUE NOT NULL  -- Email (required, no duplicates)
);

Songs Table

CREATE TABLE songs (
  song_id    INT           PRIMARY KEY,     -- Unique ID for each song
  title      VARCHAR(255)  NOT NULL,        -- Song title (required)
  artist     VARCHAR(100)  NOT NULL,        -- Artist name (required)  
  genre      VARCHAR(50)                    -- Genre (optional - can be NULL)
);

Listens Table

CREATE TABLE listens (
  listen_id    INT          PRIMARY KEY,     -- Unique ID for each listen
  user_id      INT          NOT NULL,        -- Which user (required)
  song_id      INT          NOT NULL,        -- Which song (required)
  rating       DECIMAL(2,1),                 -- Rating 0.0-5.0 (optional)
  listen_time  TIMESTAMP,                    -- When played (optional)

  FOREIGN KEY (user_id) REFERENCES users(user_id),   -- Must be valid user
  FOREIGN KEY (song_id) REFERENCES songs(song_id)    -- Must be valid song
);

SQL Schema Reference Guide

Data Types

Type	Meaning	Example
`INT`	Whole numbers	1, 42, 999
`VARCHAR(n)`	Text up to n characters	'Taylor Swift' (max 100)
`DECIMAL(p,s)`	Decimal with p total digits, s after decimal	4.5 (max 9.9)
`TIMESTAMP`	Date and time	2024-08-30 14:35:00
Modern Types	New & Popular	Used For
`JSONB`	JSON documents (PostgreSQL)	{"playlists": ["workout", "chill"]}
`BLOB`	Binary large objects	Album cover images, audio files
`VECTOR(n)`	AI embeddings	[0.1, -0.3, 0.8, ...] for song similarity

Constraints

Constraint	Purpose	Example in Our Database
`PRIMARY KEY`	Unique identifier for each row. Can be multiple columns	user_id, song_id, listen_id Or composite: (student_id, class_id)
`UNIQUE`	No duplicates allowed	email (no two users can share)
`NOT NULL`	Required - must have a value	name, title, artist
`FOREIGN KEY`	References PRIMARY KEY in another table	user_id → users, song_id → songs
(no constraint)	Optional - can be NULL	genre, rating, listen_time