Product Image Gallery

Objective

Create a product image gallery system for e-commerce with multiple product views and variant images. This enables rich product visualization and enhances the shopping experience.

Step 1: Create Products Table

Create a table for products.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    brand VARCHAR(100),
    price DECIMAL(10, 2),
    is_active BOOLEAN DEFAULT TRUE,
    primary_image_id INTEGER,
    image_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Product Images Table

Create a table for product images.

CREATE TABLE product_images (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    image IMAGE(JPEG),
    image_type VARCHAR(50) DEFAULT 'gallery',
    view_angle VARCHAR(50),
    sort_order INTEGER DEFAULT 0,
    alt_text VARCHAR(255),
    width INTEGER,
    height INTEGER,
    file_size INTEGER,
    is_primary BOOLEAN DEFAULT FALSE,
    is_visible BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Step 3: Create Image Variants Table

Store different sizes of each image.

CREATE TABLE image_variants (
    id INTEGER PRIMARY KEY,
    source_image_id INTEGER NOT NULL,
    variant_type VARCHAR(50) NOT NULL,
    image IMAGE(JPEG),
    width INTEGER,
    height INTEGER,
    file_size INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (source_image_id) REFERENCES product_images(id)
);

Step 4: Insert Sample Products

Add sample product records.

INSERT INTO products (id, sku, name, description, category, brand, price) VALUES
    (1, 'LAPTOP-001', 'Pro Laptop 15', 'High-performance laptop with 15-inch display', 'Electronics', 'TechBrand', 1299.99),
    (2, 'PHONE-001', 'SmartPhone X', 'Latest smartphone with advanced camera', 'Electronics', 'MobileMax', 899.99),
    (3, 'HEADPHONES-001', 'Wireless Pro Headphones', 'Premium noise-canceling headphones', 'Electronics', 'AudioPro', 349.99),
    (4, 'WATCH-001', 'Smart Watch Elite', 'Fitness tracking smartwatch', 'Wearables', 'TechBrand', 449.99),
    (5, 'CAMERA-001', 'DSLR Pro Camera', 'Professional digital camera', 'Photography', 'PhotoMax', 1899.99);

Step 5: Insert Product Images

Add product image records.

INSERT INTO product_images (id, product_id, image_type, view_angle, sort_order, alt_text, width, height, file_size, is_primary, is_visible) VALUES
    -- Laptop images
    (1, 1, 'gallery', 'front', 1, 'Pro Laptop 15 front view', 1200, 800, 245760, TRUE, TRUE),
    (2, 1, 'gallery', 'side', 2, 'Pro Laptop 15 side view', 1200, 800, 198400, FALSE, TRUE),
    (3, 1, 'gallery', 'keyboard', 3, 'Pro Laptop 15 keyboard detail', 1200, 800, 312000, FALSE, TRUE),
    (4, 1, 'gallery', 'ports', 4, 'Pro Laptop 15 ports and connections', 1200, 800, 178200, FALSE, TRUE),
    (5, 1, 'lifestyle', 'in-use', 5, 'Pro Laptop 15 in office setting', 1600, 1067, 425000, FALSE, TRUE),
    -- Phone images
    (6, 2, 'gallery', 'front', 1, 'SmartPhone X front view', 800, 1200, 195000, TRUE, TRUE),
    (7, 2, 'gallery', 'back', 2, 'SmartPhone X back view with camera', 800, 1200, 187500, FALSE, TRUE),
    (8, 2, 'gallery', 'side', 3, 'SmartPhone X side profile', 800, 1200, 125000, FALSE, TRUE),
    -- Headphones images
    (9, 3, 'gallery', 'front', 1, 'Wireless Pro Headphones front view', 1000, 1000, 215000, TRUE, TRUE),
    (10, 3, 'gallery', 'folded', 2, 'Wireless Pro Headphones folded', 1000, 1000, 178000, FALSE, TRUE),
    (11, 3, 'gallery', 'case', 3, 'Wireless Pro Headphones with case', 1000, 1000, 245000, FALSE, TRUE);

Step 6: Insert Image Variants

Add size variants.

INSERT INTO image_variants (id, source_image_id, variant_type, width, height, file_size) VALUES
    -- Laptop primary image variants
    (1, 1, 'thumbnail', 150, 100, 15360),
    (2, 1, 'small', 400, 267, 61440),
    (3, 1, 'medium', 800, 533, 163840),
    (4, 1, 'large', 1200, 800, 245760),
    (5, 1, 'zoom', 2400, 1600, 614400),
    -- Phone primary image variants
    (6, 6, 'thumbnail', 100, 150, 12288),
    (7, 6, 'small', 267, 400, 49152),
    (8, 6, 'medium', 533, 800, 131072),
    (9, 6, 'large', 800, 1200, 195000),
    -- Headphones primary image variants
    (10, 9, 'thumbnail', 150, 150, 18432),
    (11, 9, 'small', 400, 400, 65536),
    (12, 9, 'medium', 800, 800, 163840);

Step 7: Update Product Image Counts

Calculate image counts per product.

UPDATE products
SET image_count = (
    SELECT COUNT(*) FROM product_images
    WHERE product_images.product_id = products.id
      AND product_images.is_visible = TRUE
),
primary_image_id = (
    SELECT id FROM product_images
    WHERE product_images.product_id = products.id
      AND product_images.is_primary = TRUE
    LIMIT 1
);

SELECT name, image_count, primary_image_id FROM products;

Step 8: Get Product Gallery

Retrieve all images for a product.

SELECT
    pi.id,
    pi.view_angle,
    pi.alt_text,
    pi.width,
    pi.height,
    pi.file_size / 1024 as size_kb,
    pi.is_primary,
    pi.image_type
FROM product_images pi
WHERE pi.product_id = 1
  AND pi.is_visible = TRUE
ORDER BY pi.sort_order;

Step 9: Get Image with Variants

Get image and all its size variants.

SELECT
    pi.alt_text as image_name,
    iv.variant_type,
    iv.width,
    iv.height,
    iv.file_size / 1024 as size_kb
FROM product_images pi
INNER JOIN image_variants iv ON pi.id = iv.source_image_id
WHERE pi.id = 1
ORDER BY iv.width;

Step 10: Products with Few Images

Find products needing more images.

SELECT
    p.sku,
    p.name,
    p.image_count,
    p.category
FROM products p
WHERE p.image_count < 3
ORDER BY p.image_count;

Step 11: Image Statistics by Product

Analyze image coverage.

SELECT
    p.name,
    p.category,
    COUNT(pi.id) as total_images,
    SUM(pi.file_size) / 1048576 as total_mb,
    COUNT(CASE WHEN pi.image_type = 'gallery' THEN 1 END) as gallery_images,
    COUNT(CASE WHEN pi.image_type = 'lifestyle' THEN 1 END) as lifestyle_images
FROM products p
LEFT JOIN product_images pi ON p.id = pi.product_id
GROUP BY p.id, p.name, p.category
ORDER BY total_images DESC;

Step 12: Missing Variant Alert

Find images missing required variants.

SELECT
    p.name as product,
    pi.view_angle,
    COUNT(iv.id) as variant_count,
    'Missing variants' as alert
FROM products p
INNER JOIN product_images pi ON p.id = pi.product_id
LEFT JOIN image_variants iv ON pi.id = iv.source_image_id
WHERE pi.is_primary = TRUE
GROUP BY p.id, p.name, pi.id, pi.view_angle
HAVING COUNT(iv.id) < 4;

Cleanup (Optional)

DROP TABLE IF EXISTS image_variants;
DROP TABLE IF EXISTS product_images;
DROP TABLE IF EXISTS products;

Expected Outcomes

• Multiple product images stored
• Size variants generated
• Gallery ordering works
• Primary images identified
• Coverage tracked

Image Types

Key Concepts Learned

• Product image management
• Multiple image views
• Size variant handling
• Gallery ordering
• Coverage analysis