Video Chapter Markers

Objective

Implement chapter markers for video content to enable easy navigation. This pattern supports video players with chapter navigation, content outlines, and improved user experience.

Step 1: Create Video Table

Create a table for videos with chapter support.

CREATE TABLE chaptered_videos (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    duration_seconds INTEGER NOT NULL,
    has_chapters BOOLEAN DEFAULT FALSE,
    chapter_count INTEGER DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Chapters Table

Create a table for storing video chapters.

CREATE TABLE video_chapters (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    chapter_number INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_time_seconds DECIMAL(10, 3) NOT NULL,
    end_time_seconds DECIMAL(10, 3),
    thumbnail IMAGE(JPEG),
    is_highlight BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES chaptered_videos(id)
);

Step 3: Insert Sample Videos

Add sample video records.

INSERT INTO chaptered_videos (id, title, duration_seconds, has_chapters, chapter_count, category) VALUES
    (1, 'Complete Product Tutorial', 3600, TRUE, 8, 'tutorial'),
    (2, 'Annual Conference Keynote', 5400, TRUE, 6, 'event'),
    (3, 'Cooking Class: Italian Cuisine', 2700, TRUE, 5, 'education'),
    (4, 'Workout Video: Full Body', 1800, TRUE, 7, 'fitness'),
    (5, 'Short Product Demo', 300, FALSE, 0, 'demo');

Step 4: Insert Video Chapters

Add chapter markers for videos.

INSERT INTO video_chapters (id, video_id, chapter_number, title, description, start_time_seconds, end_time_seconds, is_highlight) VALUES
    -- Product Tutorial chapters
    (1, 1, 1, 'Introduction', 'Welcome and overview of what you will learn', 0.000, 300.000, FALSE),
    (2, 1, 2, 'Getting Started', 'Initial setup and configuration', 300.000, 600.000, FALSE),
    (3, 1, 3, 'Basic Features', 'Core functionality walkthrough', 600.000, 1200.000, TRUE),
    (4, 1, 4, 'Advanced Settings', 'Power user configurations', 1200.000, 1800.000, FALSE),
    (5, 1, 5, 'Integration Options', 'Third-party integrations', 1800.000, 2400.000, FALSE),
    (6, 1, 6, 'Tips and Tricks', 'Pro tips for productivity', 2400.000, 3000.000, TRUE),
    (7, 1, 7, 'Troubleshooting', 'Common issues and solutions', 3000.000, 3300.000, FALSE),
    (8, 1, 8, 'Conclusion', 'Summary and next steps', 3300.000, 3600.000, FALSE),
    -- Conference Keynote chapters
    (9, 2, 1, 'Opening Remarks', 'CEO welcome address', 0.000, 600.000, FALSE),
    (10, 2, 2, 'Year in Review', 'Achievements and milestones', 600.000, 1800.000, TRUE),
    (11, 2, 3, 'Product Announcements', 'New product launches', 1800.000, 3000.000, TRUE),
    (12, 2, 4, 'Customer Success Stories', 'Featured customer testimonials', 3000.000, 4200.000, FALSE),
    (13, 2, 5, 'Future Roadmap', 'Upcoming features and vision', 4200.000, 5100.000, TRUE),
    (14, 2, 6, 'Closing and Q&A', 'Final remarks and questions', 5100.000, 5400.000, FALSE),
    -- Cooking Class chapters
    (15, 3, 1, 'Ingredients Overview', 'What you will need', 0.000, 300.000, FALSE),
    (16, 3, 2, 'Preparing the Sauce', 'Making authentic tomato sauce', 300.000, 900.000, TRUE),
    (17, 3, 3, 'Making Fresh Pasta', 'Hand-rolled pasta technique', 900.000, 1800.000, TRUE),
    (18, 3, 4, 'Cooking and Assembly', 'Bringing it all together', 1800.000, 2400.000, FALSE),
    (19, 3, 5, 'Plating and Presentation', 'Final touches', 2400.000, 2700.000, FALSE);

Step 5: Get Video with Chapters

Retrieve chapters for a specific video.

SELECT
    vc.chapter_number,
    vc.title,
    vc.description,
    vc.start_time_seconds / 60 as start_minute,
    (vc.end_time_seconds - vc.start_time_seconds) / 60 as duration_minutes,
    vc.is_highlight
FROM video_chapters vc
WHERE vc.video_id = 1
ORDER BY vc.chapter_number;

Step 6: Generate Chapter Timeline

Create a formatted timeline display.

SELECT
    chapter_number,
    title,
    CAST(start_time_seconds / 60 AS INTEGER) as minute,
    CAST(start_time_seconds AS INTEGER) % 60 as second,
    is_highlight
FROM video_chapters
WHERE video_id = 2
ORDER BY chapter_number;

Step 7: Find Highlight Chapters

Get highlighted sections across all videos.

SELECT
    cv.title as video_title,
    vc.chapter_number,
    vc.title as chapter_title,
    vc.start_time_seconds / 60 as start_minute,
    (vc.end_time_seconds - vc.start_time_seconds) / 60 as duration_minutes
FROM chaptered_videos cv
INNER JOIN video_chapters vc ON cv.id = vc.video_id
WHERE vc.is_highlight = TRUE
ORDER BY cv.title, vc.chapter_number;

Step 8: Chapter Duration Analysis

Analyze chapter length distribution.

SELECT
    cv.title as video,
    COUNT(vc.id) as chapter_count,
    AVG(vc.end_time_seconds - vc.start_time_seconds) / 60 as avg_chapter_minutes,
    MIN(vc.end_time_seconds - vc.start_time_seconds) / 60 as shortest_chapter,
    MAX(vc.end_time_seconds - vc.start_time_seconds) / 60 as longest_chapter
FROM chaptered_videos cv
INNER JOIN video_chapters vc ON cv.id = vc.video_id
GROUP BY cv.id, cv.title
ORDER BY chapter_count DESC;

Step 9: Find Chapter by Time

Find which chapter contains a specific timestamp.

SELECT
    cv.title as video,
    vc.chapter_number,
    vc.title as chapter,
    vc.start_time_seconds,
    vc.end_time_seconds
FROM chaptered_videos cv
INNER JOIN video_chapters vc ON cv.id = vc.video_id
WHERE cv.id = 1
  AND 800 >= vc.start_time_seconds
  AND 800 < vc.end_time_seconds;

Step 10: Videos Without Chapters

Find videos that need chapter markers.

SELECT
    id,
    title,
    duration_seconds / 60 as minutes,
    category,
    'Needs chapters' as status
FROM chaptered_videos
WHERE has_chapters = FALSE
   OR chapter_count = 0
ORDER BY duration_seconds DESC;

Step 11: Chapter Count by Category

Analyze chaptering patterns by category.

SELECT
    cv.category,
    COUNT(DISTINCT cv.id) as video_count,
    SUM(cv.chapter_count) as total_chapters,
    AVG(cv.chapter_count) as avg_chapters_per_video
FROM chaptered_videos cv
WHERE cv.has_chapters = TRUE
GROUP BY cv.category
ORDER BY avg_chapters_per_video DESC;

Step 12: Search Chapters

Search for chapters by title or description.

SELECT
    cv.title as video,
    vc.chapter_number,
    vc.title as chapter,
    vc.description,
    vc.start_time_seconds / 60 as start_minute
FROM chaptered_videos cv
INNER JOIN video_chapters vc ON cv.id = vc.video_id
WHERE vc.title LIKE '%Product%'
   OR vc.description LIKE '%feature%'
ORDER BY cv.title, vc.chapter_number;

Cleanup (Optional)

DROP TABLE IF EXISTS video_chapters;
DROP TABLE IF EXISTS chaptered_videos;

Expected Outcomes

• Chapter markers stored with timestamps
• Navigation timeline generated
• Highlights easily identified
• Duration analysis works
• Chapter search enabled

Chapter Best Practices

Key Concepts Learned

• Chapter marker storage
• Time-based navigation
• Highlight identification
• Duration calculations
• Content organization