Video Subtitle Storage

Objective

Create a subtitle and caption storage system supporting multiple languages. This enables video accessibility, internationalization, and compliance with accessibility standards.

Step 1: Create Video Table

Create a table for videos with subtitle support.

CREATE TABLE subtitled_videos (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    duration_seconds INTEGER NOT NULL,
    default_language VARCHAR(10) DEFAULT 'en',
    subtitle_count INTEGER DEFAULT 0,
    has_captions BOOLEAN DEFAULT FALSE,
    is_accessible BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Subtitle Tracks Table

Create a table for subtitle track metadata.

CREATE TABLE subtitle_tracks (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    language_code VARCHAR(10) NOT NULL,
    language_name VARCHAR(50) NOT NULL,
    track_type VARCHAR(20) DEFAULT 'subtitles',
    is_default BOOLEAN DEFAULT FALSE,
    is_auto_generated BOOLEAN DEFAULT FALSE,
    cue_count INTEGER DEFAULT 0,
    file_format VARCHAR(10) DEFAULT 'vtt',
    created_by VARCHAR(100),
    reviewed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES subtitled_videos(id)
);

Step 3: Create Subtitle Cues Table

Store individual subtitle entries.

CREATE TABLE subtitle_cues (
    id INTEGER PRIMARY KEY,
    track_id INTEGER NOT NULL,
    cue_index INTEGER NOT NULL,
    start_time DECIMAL(10, 3) NOT NULL,
    end_time DECIMAL(10, 3) NOT NULL,
    text_content TEXT NOT NULL,
    speaker VARCHAR(100),
    style VARCHAR(50),
    position VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (track_id) REFERENCES subtitle_tracks(id)
);

Step 4: Insert Sample Videos

Add videos for subtitling.

INSERT INTO subtitled_videos (id, title, duration_seconds, default_language, has_captions) VALUES
    (1, 'Product Launch Announcement', 600, 'en', TRUE),
    (2, 'Global Marketing Campaign', 300, 'en', TRUE),
    (3, 'Training Video: Onboarding', 1200, 'en', TRUE),
    (4, 'Customer Testimonial', 180, 'en', FALSE),
    (5, 'Technical Documentation', 900, 'en', FALSE);

Step 5: Insert Subtitle Tracks

Add subtitle tracks for videos.

INSERT INTO subtitle_tracks (id, video_id, language_code, language_name, track_type, is_default, is_auto_generated, created_by, reviewed) VALUES
    -- Product Launch subtitles
    (1, 1, 'en', 'English', 'subtitles', TRUE, FALSE, 'content_team', TRUE),
    (2, 1, 'es', 'Spanish', 'subtitles', FALSE, FALSE, 'translator_es', TRUE),
    (3, 1, 'fr', 'French', 'subtitles', FALSE, FALSE, 'translator_fr', TRUE),
    (4, 1, 'de', 'German', 'subtitles', FALSE, FALSE, 'translator_de', FALSE),
    (5, 1, 'en', 'English', 'captions', FALSE, FALSE, 'content_team', TRUE),
    -- Marketing Campaign subtitles
    (6, 2, 'en', 'English', 'subtitles', TRUE, FALSE, 'content_team', TRUE),
    (7, 2, 'zh', 'Chinese', 'subtitles', FALSE, FALSE, 'translator_zh', TRUE),
    (8, 2, 'ja', 'Japanese', 'subtitles', FALSE, FALSE, 'translator_ja', TRUE),
    (9, 2, 'pt', 'Portuguese', 'subtitles', FALSE, TRUE, 'auto_translate', FALSE),
    -- Training Video subtitles
    (10, 3, 'en', 'English', 'subtitles', TRUE, FALSE, 'training_team', TRUE),
    (11, 3, 'en', 'English', 'captions', FALSE, FALSE, 'training_team', TRUE),
    (12, 3, 'es', 'Spanish', 'subtitles', FALSE, TRUE, 'auto_translate', FALSE);

Step 6: Insert Subtitle Cues

Add individual subtitle entries.

INSERT INTO subtitle_cues (id, track_id, cue_index, start_time, end_time, text_content, speaker) VALUES
    -- English subtitles for Product Launch
    (1, 1, 1, 0.000, 3.500, 'Welcome to our product launch event.', 'Host'),
    (2, 1, 2, 3.500, 7.200, 'Today we are excited to announce our latest innovation.', 'Host'),
    (3, 1, 3, 7.200, 12.000, 'This product represents two years of development.', 'CEO'),
    (4, 1, 4, 12.000, 16.500, 'Let me walk you through the key features.', 'CEO'),
    (5, 1, 5, 16.500, 21.000, 'First, we have significantly improved performance.', 'CEO'),
    -- Spanish subtitles for Product Launch
    (6, 2, 1, 0.000, 3.500, 'Bienvenidos a nuestro evento de lanzamiento.', 'Host'),
    (7, 2, 2, 3.500, 7.200, 'Hoy estamos emocionados de anunciar nuestra última innovación.', 'Host'),
    (8, 2, 3, 7.200, 12.000, 'Este producto representa dos años de desarrollo.', 'CEO'),
    (9, 2, 4, 12.000, 16.500, 'Permítanme mostrarles las características principales.', 'CEO'),
    (10, 2, 5, 16.500, 21.000, 'Primero, hemos mejorado significativamente el rendimiento.', 'CEO'),
    -- English captions (with more detail)
    (11, 5, 1, 0.000, 3.500, '[Applause] Welcome to our product launch event.', 'Host'),
    (12, 5, 2, 3.500, 7.200, 'Today we are excited to announce our latest innovation. [Crowd cheers]', 'Host'),
    (13, 5, 3, 7.200, 12.000, '[CEO takes stage] This product represents two years of development.', 'CEO');

Step 7: Update Track Statistics

Calculate cue counts.

UPDATE subtitle_tracks
SET cue_count = (
    SELECT COUNT(*) FROM subtitle_cues WHERE subtitle_cues.track_id = subtitle_tracks.id
);

UPDATE subtitled_videos
SET subtitle_count = (
    SELECT COUNT(*) FROM subtitle_tracks WHERE subtitle_tracks.video_id = subtitled_videos.id
);

SELECT v.title, v.subtitle_count FROM subtitled_videos v;

Step 8: Get Available Languages

List available subtitles for a video.

SELECT
    sv.title,
    st.language_code,
    st.language_name,
    st.track_type,
    st.is_default,
    st.is_auto_generated,
    st.reviewed
FROM subtitled_videos sv
INNER JOIN subtitle_tracks st ON sv.id = st.video_id
WHERE sv.id = 1
ORDER BY st.is_default DESC, st.language_name;

Step 9: Get Subtitle Content

Retrieve subtitles for a specific track.

SELECT
    cue_index,
    start_time,
    end_time,
    end_time - start_time as duration,
    text_content,
    speaker
FROM subtitle_cues
WHERE track_id = 1
ORDER BY cue_index;

Step 10: Find Unreviewed Translations

Get auto-generated subtitles needing review.

SELECT
    sv.title,
    st.language_name,
    st.cue_count,
    st.created_by,
    'Needs Review' as status
FROM subtitled_videos sv
INNER JOIN subtitle_tracks st ON sv.id = st.video_id
WHERE st.is_auto_generated = TRUE
  AND st.reviewed = FALSE
ORDER BY sv.title, st.language_name;

Step 11: Subtitle Coverage Report

Analyze language coverage.

SELECT
    st.language_code,
    st.language_name,
    COUNT(DISTINCT st.video_id) as video_count,
    SUM(st.cue_count) as total_cues,
    SUM(CASE WHEN st.reviewed THEN 1 ELSE 0 END) as reviewed_count
FROM subtitle_tracks st
WHERE st.track_type = 'subtitles'
GROUP BY st.language_code, st.language_name
ORDER BY video_count DESC;

Step 12: Videos Missing Subtitles

Find videos without required languages.

SELECT
    sv.title,
    sv.duration_seconds / 60 as minutes,
    COALESCE(STRING_AGG(st.language_code, ', '), 'none') as available_languages
FROM subtitled_videos sv
LEFT JOIN subtitle_tracks st ON sv.id = st.video_id AND st.track_type = 'subtitles'
GROUP BY sv.id, sv.title, sv.duration_seconds
HAVING COUNT(st.id) < 3
ORDER BY sv.title;

Cleanup (Optional)

DROP TABLE IF EXISTS subtitle_cues;
DROP TABLE IF EXISTS subtitle_tracks;
DROP TABLE IF EXISTS subtitled_videos;

Expected Outcomes

• Multi-language subtitles stored
• Captions for accessibility
• Auto-generated tracks flagged
• Review workflow enabled
• Coverage analysis available

Track Types

Key Concepts Learned

• Subtitle storage patterns
• Multi-language support
• Caption vs subtitle distinction
• Review workflow
• Accessibility compliance