Spotify Database

Overview

Three tables. Users listen to songs. Ratings and timestamps track 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: Uniquely identifies each row - no duplicates, no NULLs allowed.

Email is UNIQUE as well, but you tend to pick an ID as a key. E.g., same user could change Email later.
Composite primary keys: Multi-columns (sunid, class_id) are PRIMARY KEY in student Grades table (sunid, class_id, grade).

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: Links to another table's PRIMARY KEY - ensures referential integrity (can't reference non-existent users or songs)

Database Schema (CREATE TABLE Statements)

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

1️⃣ 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)
);

2️⃣ 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)
);

3️⃣ 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"], "premium": true}
`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