Spotify Database

Concept. The course runs on three tables: 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 1 (Mickey) played song 1 (Evermore) and rated it 4.5. Users and Songs hold the who and what; Listens records each play and joins them by key.

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 1 means Mickey (user 1) played Evermore (song 1) and rated it 4.5.

Figure 1. The whole schema on one page. Each table has a primary key (blue PK): user_id, song_id, listen_id. Listens carries two foreign keys (blue FK) that point back at the other tables, so one Listens row reads as a sentence: listen 1 is Mickey (user 1) playing Evermore (song 1), rated 4.5. 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. Reading the DDL. CREATE TABLE names each column, gives it a type (INT, DECIMAL(2,1), TIMESTAMP), and states its rules: PRIMARY KEY picks the unique id, NOT NULL marks a column required, and FOREIGN KEY ... REFERENCES ties user_id and song_id to real rows in Users and Songs so a listen can never reference a user or song that does not exist.

The figure walks Listens because it has every feature: a primary key, two foreign keys, typed columns, and required versus nullable fields. Here is 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