Podcast Episode Manager

Objective

Create a relational structure for managing podcasts with shows and episodes. This pattern enables building a complete podcast platform with multiple shows and their episodes.

Step 1: Create Podcast Shows Table

Create a table for podcast shows.

CREATE TABLE podcast_shows (
    show_id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    host VARCHAR(100),
    category VARCHAR(50),
    language VARCHAR(10) DEFAULT 'en',
    cover_image IMAGE(JPEG),
    website_url VARCHAR(255),
    rss_feed_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    total_episodes INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Episodes Table

Create a table for podcast episodes.

CREATE TABLE podcast_episodes (
    episode_id INTEGER PRIMARY KEY,
    show_id INTEGER NOT NULL,
    episode_number INTEGER,
    season_number INTEGER DEFAULT 1,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    audio_file AUDIO(MP3),
    duration_seconds INTEGER,
    file_size BIGINT,
    publish_date DATE,
    is_published BOOLEAN DEFAULT FALSE,
    download_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (show_id) REFERENCES podcast_shows(show_id)
);

Step 3: Insert Sample Podcast Shows

Add sample podcast shows.

INSERT INTO podcast_shows (show_id, title, description, host, category, website_url) VALUES
    (1, 'Tech Talk Weekly', 'Weekly discussions about the latest in technology and innovation', 'John Smith', 'Technology', 'https://techtalkweekly.com'),
    (2, 'Business Insights', 'Expert interviews and business strategy discussions', 'Sarah Johnson', 'Business', 'https://businessinsights.com'),
    (3, 'Creative Corner', 'Exploring creativity in art, design, and music', 'Mike Chen', 'Arts', 'https://creativecorner.fm'),
    (4, 'Health Matters', 'Health tips, fitness advice, and wellness discussions', 'Dr. Emily Brown', 'Health', 'https://healthmatters.com');

Step 4: Insert Podcast Episodes

Add episodes for the shows.

INSERT INTO podcast_episodes (episode_id, show_id, episode_number, season_number, title, description, duration_seconds, publish_date, is_published, download_count) VALUES
    -- Tech Talk Weekly episodes
    (1, 1, 1, 1, 'Introduction to AI', 'Exploring the basics of artificial intelligence', 2400, '2024-01-05', TRUE, 15000),
    (2, 1, 2, 1, 'The Future of Web Development', 'New frameworks and technologies in web dev', 2700, '2024-01-12', TRUE, 12500),
    (3, 1, 3, 1, 'Cloud Computing Deep Dive', 'Understanding cloud infrastructure', 3000, '2024-01-19', TRUE, 11000),
    (4, 1, 4, 1, 'Cybersecurity Essentials', 'Protecting your digital assets', 2550, '2024-01-26', TRUE, 9800),

    -- Business Insights episodes
    (5, 2, 1, 1, 'Startup Funding 101', 'How to raise capital for your startup', 1800, '2024-01-03', TRUE, 8500),
    (6, 2, 2, 1, 'Leadership in Remote Teams', 'Managing distributed workforces', 2100, '2024-01-10', TRUE, 7200),
    (7, 2, 3, 1, 'Marketing on a Budget', 'Cost-effective marketing strategies', 1950, '2024-01-17', TRUE, 6800),

    -- Creative Corner episodes
    (8, 3, 1, 1, 'Finding Your Creative Voice', 'Developing your unique style', 2200, '2024-01-08', TRUE, 5500),
    (9, 3, 2, 1, 'Digital Art Tools Review', 'Best software for digital artists', 2400, '2024-01-15', TRUE, 4800),

    -- Health Matters episodes
    (10, 4, 1, 1, 'Nutrition Basics', 'Understanding macros and micros', 1500, '2024-01-02', TRUE, 10200),
    (11, 4, 2, 1, 'Home Workout Routines', 'Effective exercises without equipment', 1800, '2024-01-09', TRUE, 9500);

Step 5: Update Show Episode Counts

Keep track of episode counts per show.

UPDATE podcast_shows
SET total_episodes = (
    SELECT COUNT(*)
    FROM podcast_episodes
    WHERE podcast_episodes.show_id = podcast_shows.show_id
      AND is_published = TRUE
);

SELECT show_id, title, total_episodes
FROM podcast_shows;

Step 6: Query Shows with Episode Counts

Get shows with their published episode counts.

SELECT
    ps.title as show_title,
    ps.host,
    ps.category,
    COUNT(pe.episode_id) as published_episodes,
    SUM(pe.duration_seconds) / 3600 as total_hours,
    SUM(pe.download_count) as total_downloads
FROM podcast_shows ps
LEFT JOIN podcast_episodes pe ON ps.show_id = pe.show_id AND pe.is_published = TRUE
GROUP BY ps.show_id, ps.title, ps.host, ps.category
ORDER BY total_downloads DESC;

Step 7: Get Episodes for a Show

Retrieve all episodes for a specific show.

SELECT
    episode_number,
    season_number,
    title,
    description,
    duration_seconds / 60 as duration_minutes,
    publish_date,
    download_count
FROM podcast_episodes
WHERE show_id = 1 AND is_published = TRUE
ORDER BY season_number, episode_number;

Step 8: Most Popular Episodes

Find the most downloaded episodes across all shows.

SELECT
    ps.title as show_title,
    pe.title as episode_title,
    pe.episode_number,
    pe.download_count,
    pe.publish_date
FROM podcast_episodes pe
INNER JOIN podcast_shows ps ON pe.show_id = ps.show_id
WHERE pe.is_published = TRUE
ORDER BY pe.download_count DESC
LIMIT 10;

Step 9: Recent Episodes Feed

Get recently published episodes.

SELECT
    ps.title as show_title,
    pe.title as episode_title,
    pe.publish_date,
    pe.duration_seconds / 60 as duration_minutes
FROM podcast_episodes pe
INNER JOIN podcast_shows ps ON pe.show_id = ps.show_id
WHERE pe.is_published = TRUE
ORDER BY pe.publish_date DESC
LIMIT 20;

Step 10: Show Statistics

Generate detailed show statistics.

SELECT
    ps.title,
    ps.category,
    COUNT(pe.episode_id) as episode_count,
    AVG(pe.duration_seconds) / 60 as avg_duration_minutes,
    AVG(pe.download_count) as avg_downloads,
    MAX(pe.publish_date) as latest_episode
FROM podcast_shows ps
LEFT JOIN podcast_episodes pe ON ps.show_id = pe.show_id
GROUP BY ps.show_id, ps.title, ps.category
ORDER BY avg_downloads DESC;

Step 11: Episodes by Category

Aggregate episodes by show category.

SELECT
    ps.category,
    COUNT(DISTINCT ps.show_id) as show_count,
    COUNT(pe.episode_id) as total_episodes,
    SUM(pe.download_count) as total_downloads
FROM podcast_shows ps
LEFT JOIN podcast_episodes pe ON ps.show_id = pe.show_id
GROUP BY ps.category
ORDER BY total_downloads DESC;

Step 12: Unpublished Episodes

Find episodes pending publication.

SELECT
    ps.title as show_title,
    pe.title as episode_title,
    pe.episode_number,
    pe.created_at
FROM podcast_episodes pe
INNER JOIN podcast_shows ps ON pe.show_id = ps.show_id
WHERE pe.is_published = FALSE
ORDER BY pe.created_at;

Cleanup (Optional)

DROP TABLE IF EXISTS podcast_episodes;
DROP TABLE IF EXISTS podcast_shows;

Expected Outcomes

• Shows and episodes properly related
• Episode counts tracked per show
• Download statistics aggregated
• Recent feed queries work
• Category groupings functional

Key Concepts Learned

• Parent-child table relationships
• Foreign key for show-episode link
• Aggregate queries across relations
• Publication workflow tracking
• Statistics and analytics queries