Product Documentation Library

Objective

Create a comprehensive product documentation management system with versioning, multi-language support, user feedback, and analytics to support technical writing teams.

Step 1: Create Products Table

Define product catalog.

CREATE TABLE doc_products (
    id INTEGER PRIMARY KEY,
    product_code VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(200) NOT NULL,
    product_line VARCHAR(100),
    current_version VARCHAR(20),
    description TEXT,
    logo IMAGE(PNG),
    documentation_url VARCHAR(500),
    support_email VARCHAR(200),
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Documentation Categories Table

Organize documentation types.

CREATE TABLE doc_categories (
    id INTEGER PRIMARY KEY,
    category_code VARCHAR(50) NOT NULL UNIQUE,
    category_name VARCHAR(100),
    parent_category_id INTEGER,
    description TEXT,
    icon_class VARCHAR(50),
    display_order INTEGER,
    FOREIGN KEY (parent_category_id) REFERENCES doc_categories(id)
);

Step 3: Create Documents Table

Store documentation metadata.

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    doc_code VARCHAR(50) NOT NULL UNIQUE,
    product_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    title VARCHAR(300) NOT NULL,
    description TEXT,
    doc_type VARCHAR(50),
    product_version VARCHAR(20),
    author_id VARCHAR(50),
    author_name VARCHAR(200),
    content_text TEXT,
    thumbnail IMAGE(JPEG),
    reading_time_minutes INTEGER,
    difficulty_level VARCHAR(20),
    prerequisites TEXT,
    status VARCHAR(50) DEFAULT 'draft',
    published_at TIMESTAMP,
    last_reviewed TIMESTAMP,
    review_due DATE,
    view_count INTEGER DEFAULT 0,
    helpful_count INTEGER DEFAULT 0,
    not_helpful_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES doc_products(id),
    FOREIGN KEY (category_id) REFERENCES doc_categories(id)
);

Step 4: Create Document Versions Table

Track version history.

CREATE TABLE doc_versions (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    version_number VARCHAR(20),
    change_summary TEXT,
    content_text TEXT,
    pdf_file PDF,
    changed_by VARCHAR(100),
    is_current BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id)
);

Step 5: Create Document Media Table

Store embedded media.

CREATE TABLE doc_media (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    media_type VARCHAR(50),
    media_title VARCHAR(200),
    description TEXT,
    image_file IMAGE(PNG),
    video_file VIDEO(MP4),
    screenshot IMAGE(PNG),
    diagram IMAGE(PNG),
    file_size_mb DECIMAL(10, 2),
    duration_seconds INTEGER,
    display_order INTEGER,
    alt_text TEXT,
    caption TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id)
);

Step 6: Create Translations Table

Support localization.

CREATE TABLE doc_translations (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    language_code VARCHAR(10) NOT NULL,
    language_name VARCHAR(50),
    title VARCHAR(300),
    description TEXT,
    content_text TEXT,
    pdf_file PDF,
    translator_id VARCHAR(50),
    translator_name VARCHAR(200),
    translation_status VARCHAR(50) DEFAULT 'pending',
    translated_at TIMESTAMP,
    reviewed_at TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id)
);

Step 7: Create User Feedback Table

Collect documentation feedback.

CREATE TABLE doc_feedback (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    user_id VARCHAR(50),
    feedback_type VARCHAR(50),
    is_helpful BOOLEAN,
    rating INTEGER,
    comment TEXT,
    improvement_suggestion TEXT,
    page_section VARCHAR(200),
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'new',
    response_text TEXT,
    responded_by VARCHAR(100),
    responded_at TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id)
);

Step 8: Insert Sample Products

Add product catalog.

INSERT INTO doc_products (id, product_code, product_name, product_line, current_version, description, status) VALUES
    (1, 'PROD-X', 'Product X', 'Enterprise Suite', '3.2.1', 'Enterprise data management platform', 'active'),
    (2, 'PROD-Y', 'Product Y', 'Enterprise Suite', '2.5.0', 'Analytics and reporting tool', 'active'),
    (3, 'PROD-Z', 'Product Z', 'Developer Tools', '1.8.3', 'API development framework', 'active'),
    (4, 'SDK-MOBILE', 'Mobile SDK', 'Developer Tools', '4.0.0', 'Mobile application development kit', 'active'),
    (5, 'CLOUD-SVC', 'Cloud Services', 'Cloud Platform', '2024.1', 'Cloud infrastructure services', 'active');

Step 9: Insert Documentation Categories

Add category structure.

INSERT INTO doc_categories (id, category_code, category_name, parent_category_id, description, display_order) VALUES
    (1, 'GET-START', 'Getting Started', NULL, 'Quick start guides and tutorials', 1),
    (2, 'USER-GUIDE', 'User Guide', NULL, 'Comprehensive user documentation', 2),
    (3, 'API-REF', 'API Reference', NULL, 'API documentation and specifications', 3),
    (4, 'TUTORIALS', 'Tutorials', NULL, 'Step-by-step tutorials', 4),
    (5, 'TROUBLESHOOT', 'Troubleshooting', NULL, 'Problem resolution guides', 5),
    (6, 'INSTALL', 'Installation', 1, 'Installation guides', 1),
    (7, 'CONFIG', 'Configuration', 1, 'Configuration guides', 2),
    (8, 'FEATURES', 'Features', 2, 'Feature documentation', 1),
    (9, 'ADMIN', 'Administration', 2, 'Admin guides', 2),
    (10, 'REST-API', 'REST API', 3, 'REST API documentation', 1);

Step 10: Insert Documents

Add documentation content.

INSERT INTO documents (id, doc_code, product_id, category_id, title, description, doc_type, product_version, author_name, reading_time_minutes, difficulty_level, status, published_at, view_count, helpful_count) VALUES
    (1, 'DOC-PX-QS-001', 1, 6, 'Quick Start Installation Guide', 'Get Product X running in 15 minutes', 'quick_start', '3.2.1', 'Technical Writer Team', 15, 'beginner', 'published', '2023-12-01 10:00:00', 12450, 892),
    (2, 'DOC-PX-UG-001', 1, 8, 'Dashboard Overview', 'Understanding the main dashboard', 'user_guide', '3.2.1', 'Technical Writer Team', 20, 'beginner', 'published', '2023-12-05 10:00:00', 8920, 654),
    (3, 'DOC-PX-API-001', 1, 10, 'REST API Getting Started', 'Introduction to the Product X API', 'api_guide', '3.2.1', 'API Documentation Team', 30, 'intermediate', 'published', '2023-12-10 10:00:00', 6540, 521),
    (4, 'DOC-PX-TUT-001', 1, 4, 'Building Your First Report', 'Step-by-step report creation tutorial', 'tutorial', '3.2.1', 'Technical Writer Team', 45, 'beginner', 'published', '2023-12-15 10:00:00', 5280, 428),
    (5, 'DOC-PY-QS-001', 2, 6, 'Product Y Installation', 'Installing Product Y on various platforms', 'quick_start', '2.5.0', 'Technical Writer Team', 20, 'beginner', 'published', '2024-01-05 10:00:00', 4120, 312),
    (6, 'DOC-PZ-API-001', 3, 10, 'Product Z API Reference', 'Complete API reference documentation', 'api_reference', '1.8.3', 'API Documentation Team', 60, 'advanced', 'published', '2024-01-10 10:00:00', 3850, 298),
    (7, 'DOC-PX-TS-001', 1, 5, 'Common Error Messages', 'Troubleshooting common errors', 'troubleshooting', '3.2.1', 'Support Team', 25, 'intermediate', 'published', '2024-01-12 10:00:00', 7820, 612),
    (8, 'DOC-SDK-QS-001', 4, 6, 'Mobile SDK Quick Start', 'Getting started with mobile development', 'quick_start', '4.0.0', 'Mobile Team', 30, 'intermediate', 'published', '2024-01-15 10:00:00', 2980, 245);

Step 11: Insert Versions and Media

Add version history and media.

INSERT INTO doc_versions (id, document_id, version_number, change_summary, changed_by, is_current, created_at) VALUES
    (1, 1, '1.0', 'Initial release', 'tech.writer', FALSE, '2023-06-01 10:00:00'),
    (2, 1, '1.1', 'Added Docker installation section', 'tech.writer', FALSE, '2023-09-01 10:00:00'),
    (3, 1, '2.0', 'Updated for version 3.2.1, added Kubernetes guide', 'tech.writer', TRUE, '2023-12-01 10:00:00'),
    (4, 2, '1.0', 'Initial dashboard documentation', 'tech.writer', FALSE, '2023-06-15 10:00:00'),
    (5, 2, '1.1', 'Added new widget documentation', 'tech.writer', TRUE, '2023-12-05 10:00:00'),
    (6, 3, '1.0', 'Initial API documentation', 'api.team', FALSE, '2023-07-01 10:00:00'),
    (7, 3, '2.0', 'Complete API v3 documentation', 'api.team', TRUE, '2023-12-10 10:00:00');

INSERT INTO doc_media (id, document_id, media_type, media_title, description, file_size_mb, display_order, alt_text) VALUES
    (1, 1, 'screenshot', 'Installation Wizard', 'Screenshot of installation wizard step 1', 0.8, 1, 'Installation wizard welcome screen'),
    (2, 1, 'diagram', 'Architecture Overview', 'System architecture diagram', 0.3, 2, 'Product X architecture showing components'),
    (3, 2, 'screenshot', 'Main Dashboard', 'Screenshot of the main dashboard', 1.2, 1, 'Dashboard with sample data'),
    (4, 2, 'screenshot', 'Widget Panel', 'Available dashboard widgets', 0.9, 2, 'Widget selection panel'),
    (5, 4, 'screenshot', 'Report Builder', 'Report builder interface', 1.5, 1, 'Report builder with sample configuration');

INSERT INTO doc_media (id, document_id, media_type, media_title, description, file_size_mb, duration_seconds, display_order) VALUES
    (6, 1, 'video', 'Installation Walkthrough', 'Video guide for installation', 45.8, 480, 3),
    (7, 4, 'video', 'Report Building Tutorial', 'Complete tutorial video', 125.4, 1200, 2);

Step 12: Insert Translations and Feedback

Add localization and feedback.

INSERT INTO doc_translations (id, document_id, language_code, language_name, title, translator_name, translation_status, translated_at) VALUES
    (1, 1, 'es', 'Spanish', 'Guía de Inicio Rápido de Instalación', 'Maria Garcia', 'published', '2024-01-05 10:00:00'),
    (2, 1, 'fr', 'French', 'Guide de Démarrage Rapide Installation', 'Pierre Dubois', 'published', '2024-01-06 10:00:00'),
    (3, 1, 'de', 'German', 'Schnellstart-Installationsanleitung', 'Hans Mueller', 'published', '2024-01-07 10:00:00'),
    (4, 1, 'ja', 'Japanese', 'クイックスタートインストールガイド', 'Yuki Tanaka', 'in_review', '2024-01-10 10:00:00'),
    (5, 2, 'es', 'Spanish', 'Descripción General del Panel de Control', 'Maria Garcia', 'published', '2024-01-08 10:00:00');

INSERT INTO doc_feedback (id, document_id, user_id, feedback_type, is_helpful, rating, comment, improvement_suggestion, submitted_at, status) VALUES
    (1, 1, 'user-001', 'rating', TRUE, 5, 'Very clear and helpful guide!', NULL, '2024-01-10 14:00:00', 'acknowledged'),
    (2, 1, 'user-002', 'improvement', TRUE, 4, 'Good but could use more screenshots', 'Add screenshots for each step', '2024-01-11 10:00:00', 'in_review'),
    (3, 3, 'user-003', 'question', NULL, NULL, 'How do I authenticate with OAuth2?', NULL, '2024-01-12 16:00:00', 'responded'),
    (4, 7, 'user-004', 'rating', TRUE, 5, 'Saved me hours of debugging!', NULL, '2024-01-13 09:00:00', 'acknowledged'),
    (5, 4, 'user-005', 'improvement', TRUE, 3, 'Tutorial is outdated', 'Please update for version 3.2', '2024-01-14 11:00:00', 'new');

Step 13: Documentation Library View

Browse available docs.

SELECT
    d.doc_code,
    dp.product_name,
    dc.category_name,
    d.title,
    d.doc_type,
    d.product_version,
    d.difficulty_level,
    d.reading_time_minutes,
    d.view_count,
    d.helpful_count,
    COUNT(DISTINCT dt.language_code) as translations_available
FROM documents d
INNER JOIN doc_products dp ON d.product_id = dp.id
INNER JOIN doc_categories dc ON d.category_id = dc.id
LEFT JOIN doc_translations dt ON d.id = dt.document_id AND dt.translation_status = 'published'
WHERE d.status = 'published'
GROUP BY d.id, d.doc_code, dp.product_name, dc.category_name, d.title, d.doc_type, d.product_version, d.difficulty_level, d.reading_time_minutes, d.view_count, d.helpful_count
ORDER BY d.view_count DESC;

Step 14: Documentation Quality Report

Analyze feedback metrics.

SELECT
    d.title,
    dp.product_name,
    d.view_count,
    d.helpful_count,
    d.not_helpful_count,
    CASE
        WHEN d.helpful_count + d.not_helpful_count > 0
        THEN d.helpful_count * 100.0 / (d.helpful_count + d.not_helpful_count)
        ELSE 0
    END as helpful_percentage,
    COUNT(df.id) as feedback_count,
    AVG(df.rating) as avg_rating
FROM documents d
INNER JOIN doc_products dp ON d.product_id = dp.id
LEFT JOIN doc_feedback df ON d.id = df.document_id
WHERE d.status = 'published'
GROUP BY d.id, d.title, dp.product_name, d.view_count, d.helpful_count, d.not_helpful_count
ORDER BY d.view_count DESC;

Step 15: Translation Coverage Report

Track localization status.

SELECT
    dp.product_name,
    d.title,
    COUNT(CASE WHEN dt.translation_status = 'published' THEN 1 END) as published_translations,
    COUNT(CASE WHEN dt.translation_status = 'in_review' THEN 1 END) as in_review,
    COUNT(CASE WHEN dt.translation_status = 'pending' THEN 1 END) as pending,
    STRING_AGG(CASE WHEN dt.translation_status = 'published' THEN dt.language_code END, ', ') as available_languages
FROM documents d
INNER JOIN doc_products dp ON d.product_id = dp.id
LEFT JOIN doc_translations dt ON d.id = dt.document_id
WHERE d.status = 'published'
GROUP BY d.id, dp.product_name, d.title
ORDER BY dp.product_name, d.title;

Cleanup (Optional)

DROP TABLE IF EXISTS doc_feedback;
DROP TABLE IF EXISTS doc_translations;
DROP TABLE IF EXISTS doc_media;
DROP TABLE IF EXISTS doc_versions;
DROP TABLE IF EXISTS documents;
DROP TABLE IF EXISTS doc_categories;
DROP TABLE IF EXISTS doc_products;

Expected Outcomes

• Documentation organized by product
• Version history maintained
• Multiple languages supported
• User feedback collected
• Analytics tracked

Key Concepts Learned

• Documentation management
• Version control
• Localization support
• Feedback collection
• Quality metrics