Video Series Management

Objective

Create a system for managing video series with seasons and episodes. This pattern supports episodic content, course modules, and serialized video delivery.

Step 1: Create Series Table

Create a table for video series.

CREATE TABLE video_series (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    cover_image IMAGE(JPEG),
    series_type VARCHAR(50) DEFAULT 'series',
    total_seasons INTEGER DEFAULT 1,
    total_episodes INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'ongoing',
    genre VARCHAR(50),
    creator VARCHAR(100),
    release_date DATE,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Seasons Table

Create a table for seasons within series.

CREATE TABLE series_seasons (
    id INTEGER PRIMARY KEY,
    series_id INTEGER NOT NULL,
    season_number INTEGER NOT NULL,
    title VARCHAR(255),
    description TEXT,
    episode_count INTEGER DEFAULT 0,
    release_date DATE,
    is_complete BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (series_id) REFERENCES video_series(id)
);

Step 3: Create Episodes Table

Create a table for individual episodes.

CREATE TABLE series_episodes (
    id INTEGER PRIMARY KEY,
    series_id INTEGER NOT NULL,
    season_id INTEGER NOT NULL,
    episode_number INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    video_file VIDEO(MP4),
    thumbnail IMAGE(JPEG),
    duration_seconds INTEGER,
    release_date DATE,
    view_count INTEGER DEFAULT 0,
    is_premium BOOLEAN DEFAULT FALSE,
    is_published BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (series_id) REFERENCES video_series(id),
    FOREIGN KEY (season_id) REFERENCES series_seasons(id)
);

Step 4: Insert Sample Series

Add sample video series.

INSERT INTO video_series (id, title, description, series_type, total_seasons, status, genre, creator, release_date) VALUES
    (1, 'Learn Python Programming', 'Complete Python course from basics to advanced', 'course', 3, 'completed', 'Education', 'Tech Academy', '2024-01-01'),
    (2, 'Web Development Bootcamp', 'Full-stack web development series', 'course', 2, 'ongoing', 'Education', 'Code Masters', '2024-02-15'),
    (3, 'Tech Talk Weekly', 'Weekly technology news and discussions', 'series', 1, 'ongoing', 'Technology', 'Tech News Team', '2024-01-10'),
    (4, 'Startup Stories', 'Documentary series about successful startups', 'documentary', 2, 'completed', 'Business', 'Startup Media', '2023-06-01');

Step 5: Insert Seasons

Add seasons to series.

INSERT INTO series_seasons (id, series_id, season_number, title, description, episode_count, release_date, is_complete) VALUES
    -- Python course seasons
    (1, 1, 1, 'Python Fundamentals', 'Basic Python concepts and syntax', 10, '2024-01-01', TRUE),
    (2, 1, 2, 'Intermediate Python', 'OOP, modules, and file handling', 8, '2024-02-01', TRUE),
    (3, 1, 3, 'Advanced Python', 'Decorators, generators, and async', 6, '2024-03-01', TRUE),
    -- Web Dev seasons
    (4, 2, 1, 'Frontend Basics', 'HTML, CSS, and JavaScript', 12, '2024-02-15', TRUE),
    (5, 2, 2, 'Backend Development', 'Server-side programming', 8, '2024-04-01', FALSE),
    -- Tech Talk season
    (6, 3, 1, '2024 Episodes', 'Weekly episodes for 2024', 20, '2024-01-10', FALSE),
    -- Startup Stories seasons
    (7, 4, 1, 'Tech Giants Origins', 'How tech giants started', 5, '2023-06-01', TRUE),
    (8, 4, 2, 'Rising Stars', 'New startups to watch', 4, '2023-09-01', TRUE);

Step 6: Insert Episodes

Add episodes to seasons.

INSERT INTO series_episodes (id, series_id, season_id, episode_number, title, description, duration_seconds, release_date, view_count, is_premium) VALUES
    -- Python Fundamentals episodes
    (1, 1, 1, 1, 'Introduction to Python', 'What is Python and why learn it', 900, '2024-01-01', 50000, FALSE),
    (2, 1, 1, 2, 'Variables and Data Types', 'Working with variables', 1200, '2024-01-03', 45000, FALSE),
    (3, 1, 1, 3, 'Control Flow', 'If statements and loops', 1500, '2024-01-05', 42000, FALSE),
    (4, 1, 1, 4, 'Functions', 'Creating and using functions', 1800, '2024-01-07', 38000, FALSE),
    (5, 1, 1, 5, 'Lists and Tuples', 'Working with sequences', 1400, '2024-01-09', 35000, FALSE),
    -- Intermediate Python episodes
    (6, 1, 2, 1, 'Object-Oriented Programming', 'Classes and objects', 2100, '2024-02-01', 28000, TRUE),
    (7, 1, 2, 2, 'Inheritance', 'Class inheritance patterns', 1800, '2024-02-04', 25000, TRUE),
    (8, 1, 2, 3, 'Modules and Packages', 'Organizing code', 1500, '2024-02-07', 23000, TRUE),
    -- Frontend Basics episodes
    (9, 2, 4, 1, 'HTML Fundamentals', 'HTML structure and tags', 1200, '2024-02-15', 35000, FALSE),
    (10, 2, 4, 2, 'CSS Basics', 'Styling web pages', 1500, '2024-02-18', 32000, FALSE),
    (11, 2, 4, 3, 'CSS Layouts', 'Flexbox and Grid', 1800, '2024-02-21', 30000, FALSE),
    (12, 2, 4, 4, 'JavaScript Intro', 'Getting started with JS', 1600, '2024-02-24', 28000, FALSE);

Step 7: Update Episode Counts

Calculate episode totals.

UPDATE series_seasons
SET episode_count = (
    SELECT COUNT(*) FROM series_episodes
    WHERE series_episodes.season_id = series_seasons.id
);

UPDATE video_series
SET total_episodes = (
    SELECT COUNT(*) FROM series_episodes
    WHERE series_episodes.series_id = video_series.id
);

SELECT title, total_seasons, total_episodes FROM video_series;

Step 8: Get Series Overview

Get complete series information.

SELECT
    vs.title as series,
    vs.series_type,
    vs.total_seasons,
    vs.total_episodes,
    vs.status,
    vs.genre
FROM video_series vs
ORDER BY vs.title;

Step 9: Get Season Episodes

List all episodes in a season.

SELECT
    se.episode_number,
    se.title,
    se.duration_seconds / 60 as minutes,
    se.view_count,
    se.is_premium,
    se.release_date
FROM series_episodes se
WHERE se.season_id = 1
ORDER BY se.episode_number;

Step 10: Series Progress Tracking

Get series with watch statistics.

SELECT
    vs.title,
    ss.season_number,
    ss.title as season_title,
    ss.episode_count,
    SUM(se.view_count) as total_views,
    SUM(se.duration_seconds) / 3600 as total_hours
FROM video_series vs
INNER JOIN series_seasons ss ON vs.id = ss.series_id
LEFT JOIN series_episodes se ON ss.id = se.season_id
GROUP BY vs.id, vs.title, ss.id, ss.season_number, ss.title, ss.episode_count
ORDER BY vs.title, ss.season_number;

Step 11: Find Next Episode

Get next unwatched episode (simulated).

SELECT
    vs.title as series,
    'S' || ss.season_number || 'E' || se.episode_number as episode_code,
    se.title as episode_title,
    se.duration_seconds / 60 as minutes
FROM video_series vs
INNER JOIN series_seasons ss ON vs.id = ss.series_id
INNER JOIN series_episodes se ON ss.id = se.season_id
WHERE vs.id = 1
  AND se.is_published = TRUE
ORDER BY ss.season_number, se.episode_number
LIMIT 1;

Step 12: Popular Episodes

Find most viewed episodes across all series.

SELECT
    vs.title as series,
    'S' || ss.season_number || 'E' || se.episode_number as code,
    se.title as episode,
    se.view_count,
    se.is_premium
FROM series_episodes se
INNER JOIN series_seasons ss ON se.season_id = ss.id
INNER JOIN video_series vs ON se.series_id = vs.id
ORDER BY se.view_count DESC
LIMIT 10;

Cleanup (Optional)

DROP TABLE IF EXISTS series_episodes;
DROP TABLE IF EXISTS series_seasons;
DROP TABLE IF EXISTS video_series;

Expected Outcomes

• Series with seasons created
• Episodes organized sequentially
• View counts tracked
• Progress navigation works
• Premium content flagged

Series Types

Key Concepts Learned

• Series-season-episode hierarchy
• Sequential episode ordering
• Progress tracking queries
• View statistics aggregation
• Premium content management