Audio Playlist System

Objective

Create a playlist system for organizing audio tracks in custom order. This pattern enables building music players, podcast queues, and curated audio collections.

Step 1: Create Tracks Table

Create a table for audio tracks.

CREATE TABLE audio_tracks (
    track_id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    artist VARCHAR(100),
    album VARCHAR(255),
    audio_file AUDIO(MP3),
    duration_seconds INTEGER,
    genre VARCHAR(50),
    release_year INTEGER,
    play_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Playlists Table

Create a table for playlist metadata.

CREATE TABLE playlists (
    playlist_id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    cover_image IMAGE(JPEG),
    owner_id VARCHAR(50) NOT NULL,
    is_public BOOLEAN DEFAULT FALSE,
    total_tracks INTEGER DEFAULT 0,
    total_duration_seconds INTEGER DEFAULT 0,
    play_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create Playlist Items Table

Create a junction table for playlist track ordering.

CREATE TABLE playlist_items (
    id INTEGER PRIMARY KEY,
    playlist_id INTEGER NOT NULL,
    track_id INTEGER NOT NULL,
    position INTEGER NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    added_by VARCHAR(50),
    FOREIGN KEY (playlist_id) REFERENCES playlists(playlist_id),
    FOREIGN KEY (track_id) REFERENCES audio_tracks(track_id)
);

Step 4: Insert Sample Tracks

Add sample audio tracks.

INSERT INTO audio_tracks (track_id, title, artist, album, duration_seconds, genre, release_year) VALUES
    (1, 'Morning Light', 'Sunrise Band', 'New Dawn', 245, 'Pop', 2023),
    (2, 'City Streets', 'Urban Sound', 'Metropolitan', 312, 'Electronic', 2024),
    (3, 'Mountain Echo', 'Nature Collective', 'Wilderness', 198, 'Ambient', 2023),
    (4, 'Dance Floor', 'Beat Masters', 'Club Nights', 276, 'Electronic', 2024),
    (5, 'Acoustic Dreams', 'Solo Artist', 'Unplugged', 224, 'Acoustic', 2022),
    (6, 'Electric Thunder', 'Rock Brigade', 'Power Up', 298, 'Rock', 2023),
    (7, 'Jazz Cafe', 'Smooth Quartet', 'Evening Sessions', 356, 'Jazz', 2024),
    (8, 'Summer Breeze', 'Beach Vibes', 'Coastal', 267, 'Pop', 2023),
    (9, 'Night Drive', 'Synth Wave', 'Neon Lights', 289, 'Electronic', 2024),
    (10, 'Classical Morning', 'Orchestra One', 'Classics', 420, 'Classical', 2022);

Step 5: Create Sample Playlists

Add sample playlists.

INSERT INTO playlists (playlist_id, name, description, owner_id, is_public) VALUES
    (1, 'Morning Motivation', 'Upbeat tracks to start the day', 'user_001', TRUE),
    (2, 'Focus Mode', 'Calm music for concentration', 'user_001', FALSE),
    (3, 'Weekend Party', 'Dance tracks for the weekend', 'user_002', TRUE),
    (4, 'Relaxation', 'Soothing sounds for unwinding', 'user_001', TRUE),
    (5, 'Road Trip Mix', 'Perfect for long drives', 'user_003', TRUE);

Step 6: Add Tracks to Playlists

Populate playlists with tracks in order.

INSERT INTO playlist_items (id, playlist_id, track_id, position, added_by) VALUES
    -- Morning Motivation playlist
    (1, 1, 1, 1, 'user_001'),
    (2, 1, 8, 2, 'user_001'),
    (3, 1, 6, 3, 'user_001'),
    (4, 1, 4, 4, 'user_001'),

    -- Focus Mode playlist
    (5, 2, 3, 1, 'user_001'),
    (6, 2, 5, 2, 'user_001'),
    (7, 2, 10, 3, 'user_001'),
    (8, 2, 7, 4, 'user_001'),

    -- Weekend Party playlist
    (9, 3, 4, 1, 'user_002'),
    (10, 3, 2, 2, 'user_002'),
    (11, 3, 9, 3, 'user_002'),

    -- Relaxation playlist
    (12, 4, 3, 1, 'user_001'),
    (13, 4, 7, 2, 'user_001'),
    (14, 4, 10, 3, 'user_001'),
    (15, 4, 5, 4, 'user_001');

Step 7: Update Playlist Statistics

Calculate playlist totals.

UPDATE playlists
SET total_tracks = (
    SELECT COUNT(*) FROM playlist_items WHERE playlist_items.playlist_id = playlists.playlist_id
),
total_duration_seconds = (
    SELECT COALESCE(SUM(t.duration_seconds), 0)
    FROM playlist_items pi
    INNER JOIN audio_tracks t ON pi.track_id = t.track_id
    WHERE pi.playlist_id = playlists.playlist_id
);

SELECT
    name,
    total_tracks,
    total_duration_seconds / 60 as duration_minutes
FROM playlists;

Step 8: Get Playlist Contents

Retrieve tracks in playlist order.

SELECT
    pi.position,
    t.title,
    t.artist,
    t.duration_seconds,
    t.genre
FROM playlist_items pi
INNER JOIN audio_tracks t ON pi.track_id = t.track_id
WHERE pi.playlist_id = 1
ORDER BY pi.position;

Step 9: Reorder Playlist Tracks

Move a track to a new position.

-- Move track from position 4 to position 2
UPDATE playlist_items
SET position = position + 1
WHERE playlist_id = 1 AND position >= 2 AND position < 4;

UPDATE playlist_items
SET position = 2
WHERE playlist_id = 1 AND track_id = 4;

SELECT position, track_id
FROM playlist_items
WHERE playlist_id = 1
ORDER BY position;

Step 10: Find Public Playlists

Query public playlists for discovery.

SELECT
    p.name,
    p.description,
    p.owner_id,
    p.total_tracks,
    p.total_duration_seconds / 60 as duration_minutes,
    p.play_count
FROM playlists p
WHERE p.is_public = TRUE
ORDER BY p.play_count DESC;

Step 11: Track Playlist Appearances

Find which playlists contain a track.

SELECT
    p.name as playlist_name,
    pi.position,
    p.owner_id
FROM playlist_items pi
INNER JOIN playlists p ON pi.playlist_id = p.playlist_id
WHERE pi.track_id = 4
ORDER BY p.name;

Step 12: User Playlist Summary

Get playlist summary for a user.

SELECT
    owner_id,
    COUNT(*) as playlist_count,
    SUM(total_tracks) as total_tracks,
    SUM(total_duration_seconds) / 3600 as total_hours,
    SUM(CASE WHEN is_public THEN 1 ELSE 0 END) as public_playlists
FROM playlists
GROUP BY owner_id
ORDER BY playlist_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS playlist_items;
DROP TABLE IF EXISTS playlists;
DROP TABLE IF EXISTS audio_tracks;

Expected Outcomes

• Playlists created with metadata
• Tracks ordered by position
• Playlist statistics calculated
• Reordering updates positions
• Public/private visibility works

Key Concepts Learned

• Many-to-many with ordering
• Position-based track ordering
• Playlist statistics calculation
• Reordering logic
• User content management