Music Library and Playlists

Design the schema for a music streaming library: artists, albums, and tracks; user playlists in a user-defined order; liked songs; follows; and play history that feeds Recently Played and monthly listener counts.

Clarifying Questions

How long can a playlist get, and how often do users reorder it?
Can a playlist contain the same track twice?
Can several people edit one playlist?
How much play history do we keep at full detail?
Can tracks and albums credit several artists, such as features?

Core Requirements

Catalog artists, albums, and tracks with many-to-many artist credits.
Create playlists and add, remove, and reorder tracks, allowing duplicates.
Like tracks and follow artists, playlists, and users.
Record every play with the time listened.
Show Recently Played and a user's top artists for the month.
Show monthly listeners on each artist page.

Canonical schema (PostgreSQL)

CREATE TABLE artists (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  verified BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE albums (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  album_type TEXT NOT NULL CHECK (album_type IN ('album', 'single', 'compilation')),
  release_date DATE
);

CREATE TABLE album_artists (
  album_id BIGINT NOT NULL REFERENCES albums(id) ON DELETE CASCADE,
  artist_id BIGINT NOT NULL REFERENCES artists(id),
  position SMALLINT NOT NULL,
  PRIMARY KEY (album_id, artist_id)
);

CREATE TABLE tracks (
  id BIGSERIAL PRIMARY KEY,
  album_id BIGINT NOT NULL REFERENCES albums(id),
  title TEXT NOT NULL,
  disc_number SMALLINT NOT NULL DEFAULT 1,
  track_number SMALLINT NOT NULL,
  duration_ms INTEGER NOT NULL,
  explicit BOOLEAN NOT NULL DEFAULT false,
  isrc TEXT UNIQUE,
  available BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE track_artists (
  track_id BIGINT NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
  artist_id BIGINT NOT NULL REFERENCES artists(id),
  role TEXT NOT NULL CHECK (role IN ('primary', 'featured')),
  position SMALLINT NOT NULL,
  PRIMARY KEY (track_id, artist_id)
);

CREATE TABLE playlists (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID NOT NULL,
  name TEXT NOT NULL,
  is_public BOOLEAN NOT NULL DEFAULT true,
  is_collaborative BOOLEAN NOT NULL DEFAULT false,
  track_count INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE playlist_items (
  id BIGSERIAL PRIMARY KEY,
  playlist_id UUID NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
  track_id BIGINT NOT NULL REFERENCES tracks(id),
  position_key TEXT COLLATE "C" NOT NULL,
  added_by UUID NOT NULL,
  added_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (playlist_id, position_key)
);

CREATE TABLE liked_tracks (
  user_id UUID NOT NULL,
  track_id BIGINT NOT NULL REFERENCES tracks(id),
  liked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, track_id)
);

CREATE TABLE follows (
  user_id UUID NOT NULL,
  target_kind TEXT NOT NULL CHECK (target_kind IN ('artist', 'playlist', 'user')),
  target_id TEXT NOT NULL,
  followed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, target_kind, target_id)
);

CREATE TABLE plays (
  user_id UUID NOT NULL,
  track_id BIGINT NOT NULL,
  played_at TIMESTAMPTZ NOT NULL,
  ms_played INTEGER NOT NULL,
  context_kind TEXT,
  context_id TEXT
) PARTITION BY RANGE (played_at);

CREATE TABLE artist_monthly_listeners (
  artist_id BIGINT NOT NULL REFERENCES artists(id),
  month DATE NOT NULL,
  listeners INTEGER NOT NULL,
  PRIMARY KEY (artist_id, month)
);

Key Indexes

UNIQUE (playlist_id, position_key) on playlist_items

Renders a playlist in order with one range scan, and two items can never claim the same slot.

CREATE INDEX idx_liked_user_recent ON liked_tracks(user_id, liked_at DESC)

Liked Songs shows newest first; the primary key alone can't serve that order.

CREATE INDEX idx_plays_user_recent ON plays(user_id, played_at DESC)

Recently Played reads the last few rows for one user from the newest partition.

CREATE INDEX idx_track_artists_artist ON track_artists(artist_id, track_id)

The artist page lists every track the artist appears on, features included.

CREATE INDEX idx_follows_target ON follows(target_kind, target_id)

Counts followers and finds who to notify when an artist releases music.

Key Decisions to Defend

playlist_items has its own surrogate id, so one track can appear twice and (playlist_id, track_id) stays non-unique.
Order uses a sortable string position key (LexoRank style), so moving a track rewrites one row; integer positions would rewrite every row after it in a 10,000-track playlist.
Artist credits are many-to-many through track_artists with a role, so features and multi-artist albums need no special cases.
plays is append-only and partitioned by time; a batch job computes monthly listeners into artist_monthly_listeners, since a distinct count over billions of rows can't run per page view.
playlists.track_count is denormalized and updated in the same transaction as item inserts and deletes, so library views never count items.

Likely follow-ups

· Position keys grow long after many inserts between the same two items, how do you rebalance?
· Two collaborators reorder the same playlist at the same moment.
· Add yearly listening stats without scanning a year of plays per user at request time.
· A track leaves the catalog for licensing reasons, what happens to playlists that contain it?

Dive Deeper

Music library schemas test many-to-many modeling and user-defined order. Ordering is the main probe: integer positions are simple but make a move cost proportional to playlist length, while fractional or string keys make a move one row at the price of occasional rebalancing. Interviewers also check that play history is treated as an event stream with precomputed aggregates, since live counts over raw plays cannot serve an artist page.

All Database Design Prompts