Video Category Organization

Objective

Create a hierarchical category system for organizing video content. This enables structured content browsing, improved discovery, and organized video libraries.

Step 1: Create Categories Table

Create a hierarchical category structure.

CREATE TABLE video_categories (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id INTEGER,
    level INTEGER DEFAULT 0,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    video_count INTEGER DEFAULT 0,
    icon VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES video_categories(id)
);

Step 2: Create Videos Table

Create a table for categorized videos.

CREATE TABLE categorized_videos (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    description TEXT,
    category_id INTEGER NOT NULL,
    duration_seconds INTEGER,
    view_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    publish_status VARCHAR(20) DEFAULT 'published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES video_categories(id)
);

Step 3: Insert Categories

Create the category hierarchy.

INSERT INTO video_categories (id, name, slug, description, parent_id, level, sort_order, icon) VALUES
    -- Top level categories
    (1, 'Education', 'education', 'Educational and learning content', NULL, 0, 1, 'book'),
    (2, 'Entertainment', 'entertainment', 'Entertainment and leisure content', NULL, 0, 2, 'film'),
    (3, 'Business', 'business', 'Business and professional content', NULL, 0, 3, 'briefcase'),
    (4, 'Technology', 'technology', 'Technology and software content', NULL, 0, 4, 'cpu'),
    -- Education subcategories
    (5, 'Tutorials', 'tutorials', 'Step-by-step learning guides', 1, 1, 1, 'play-circle'),
    (6, 'Courses', 'courses', 'Complete learning courses', 1, 1, 2, 'graduation-cap'),
    (7, 'Webinars', 'webinars', 'Live and recorded webinars', 1, 1, 3, 'video'),
    -- Entertainment subcategories
    (8, 'Vlogs', 'vlogs', 'Personal video blogs', 2, 1, 1, 'user'),
    (9, 'Reviews', 'reviews', 'Product and service reviews', 2, 1, 2, 'star'),
    (10, 'Behind the Scenes', 'behind-scenes', 'Making-of content', 2, 1, 3, 'camera'),
    -- Business subcategories
    (11, 'Marketing', 'marketing', 'Marketing and promotional content', 3, 1, 1, 'megaphone'),
    (12, 'Training', 'training', 'Employee training videos', 3, 1, 2, 'users'),
    (13, 'Presentations', 'presentations', 'Business presentations', 3, 1, 3, 'presentation'),
    -- Technology subcategories
    (14, 'Software Demos', 'software-demos', 'Software demonstrations', 4, 1, 1, 'monitor'),
    (15, 'Tech News', 'tech-news', 'Technology news and updates', 4, 1, 2, 'newspaper'),
    (16, 'Coding', 'coding', 'Programming tutorials', 4, 1, 3, 'code');

Step 4: Insert Sample Videos

Add videos to categories.

INSERT INTO categorized_videos (id, title, description, category_id, duration_seconds, view_count, is_featured) VALUES
    -- Tutorials
    (1, 'Getting Started Guide', 'Complete beginner guide', 5, 900, 15000, TRUE),
    (2, 'Advanced Techniques', 'Pro tips and tricks', 5, 1200, 8500, FALSE),
    -- Courses
    (3, 'Python Fundamentals', 'Learn Python from scratch', 6, 7200, 25000, TRUE),
    (4, 'Data Science Basics', 'Introduction to data science', 6, 5400, 18000, FALSE),
    -- Marketing
    (5, 'Brand Story', 'Our company journey', 11, 300, 5000, TRUE),
    (6, 'Product Launch 2024', 'New product announcement', 11, 600, 12000, TRUE),
    -- Software Demos
    (7, 'Feature Walkthrough', 'Complete feature tour', 14, 1500, 9000, FALSE),
    (8, 'API Integration Guide', 'How to integrate our API', 14, 1800, 6500, FALSE),
    -- Coding
    (9, 'JavaScript Basics', 'JS fundamentals tutorial', 16, 2400, 20000, TRUE),
    (10, 'React Components', 'Building React components', 16, 1800, 15000, FALSE);

Step 5: Update Category Video Counts

Calculate videos per category.

UPDATE video_categories
SET video_count = (
    SELECT COUNT(*) FROM categorized_videos
    WHERE categorized_videos.category_id = video_categories.id
);

SELECT name, video_count FROM video_categories WHERE video_count > 0;

Step 6: Get Category Hierarchy

Display the category tree.

SELECT
    CASE
        WHEN level = 0 THEN name
        ELSE '  └─ ' || name
    END as category,
    slug,
    video_count,
    icon
FROM video_categories
WHERE is_active = TRUE
ORDER BY
    COALESCE(parent_id, id),
    level,
    sort_order;

Step 7: Get Videos by Category

List videos in a specific category.

SELECT
    cv.title,
    cv.description,
    cv.duration_seconds / 60 as minutes,
    cv.view_count,
    cv.is_featured
FROM categorized_videos cv
WHERE cv.category_id = 5
ORDER BY cv.view_count DESC;

Step 8: Get Subcategories

Find all subcategories of a parent.

SELECT
    child.name,
    child.slug,
    child.description,
    child.video_count
FROM video_categories parent
INNER JOIN video_categories child ON parent.id = child.parent_id
WHERE parent.slug = 'education'
ORDER BY child.sort_order;

Step 9: Category Statistics

Get statistics for each main category.

SELECT
    parent.name as main_category,
    COUNT(DISTINCT child.id) as subcategory_count,
    SUM(child.video_count) + parent.video_count as total_videos
FROM video_categories parent
LEFT JOIN video_categories child ON parent.id = child.parent_id
WHERE parent.parent_id IS NULL
GROUP BY parent.id, parent.name, parent.video_count
ORDER BY total_videos DESC;

Step 10: Featured Videos by Category

Get featured videos grouped by category.

SELECT
    vc.name as category,
    cv.title,
    cv.view_count,
    cv.duration_seconds / 60 as minutes
FROM categorized_videos cv
INNER JOIN video_categories vc ON cv.category_id = vc.id
WHERE cv.is_featured = TRUE
ORDER BY vc.name, cv.view_count DESC;

Step 11: Most Popular Categories

Find categories with most views.

SELECT
    vc.name,
    vc.slug,
    COUNT(cv.id) as video_count,
    SUM(cv.view_count) as total_views,
    AVG(cv.view_count) as avg_views
FROM video_categories vc
INNER JOIN categorized_videos cv ON vc.id = cv.category_id
GROUP BY vc.id, vc.name, vc.slug
ORDER BY total_views DESC;

Step 12: Category Breadcrumb

Build category breadcrumb path.

SELECT
    child.name as current_category,
    parent.name as parent_category,
    parent.slug || '/' || child.slug as full_path
FROM video_categories child
LEFT JOIN video_categories parent ON child.parent_id = parent.id
WHERE child.level > 0
ORDER BY parent.name, child.name;

Cleanup (Optional)

DROP TABLE IF EXISTS categorized_videos;
DROP TABLE IF EXISTS video_categories;

Expected Outcomes

• Hierarchical categories created
• Videos organized by category
• Subcategories linked properly
• Statistics calculated
• Navigation paths built

Category Levels

Key Concepts Learned

• Hierarchical category design
• Parent-child relationships
• Category statistics
• Video organization
• Navigation breadcrumbs