Spotify Database

Concept. We use three tables in this course: Users, Songs, and Listens. Listens links a user to a song with foreign keys, and records the rating and time.

Intuition. One Listens row reads as a sentence: user 3 (Daffy) played song 2 (Willow) and rated it 4.9. Users and Songs hold the who and what.

The Three Tables

The Spotify schema: Users, Songs, and Listens shown together. Users has primary key user_id; Songs has primary key song_id; Listens has primary key listen_id and two foreign keys, user_id pointing at Users.user_id and song_id at Songs.song_id. Decoded: listen 8 means Daffy (user 3) played Willow (song 2) and rated it 4.9, with the 3 and the 2 in that row each boxed and linked to the row they match.

Figure 1. All three tables, and the keys that tie them together: each has a primary key (user_id, song_id, listen_id), and Listens carries two foreign keys that point back at the other tables, so one Listens row reads as a sentence: listen 8 is Daffy (user 3) playing Willow (song 2), rated 4.9. Every query in the course joins these three tables along those IDs.

How the Schema Is Defined

The CREATE TABLE listens statement annotated: INT, DECIMAL(2,1) and TIMESTAMP are the column types; PRIMARY KEY marks listen_id as the unique row id and NOT NULL marks required columns; two FOREIGN KEY ... REFERENCES clauses tie user_id to a real Users row and song_id to a real Songs row, so the database rejects a listen that points at a user or song that does not exist.

Figure 2. CREATE TABLE assigns each column a name, a type (INT, DECIMAL(2,1), TIMESTAMP), and constraints. PRIMARY KEY selects the unique row identifier. NOT NULL requires a value. FOREIGN KEY ... REFERENCES constrains user_id and song_id to values that exist in the parent table.

The DDL for all three 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