Product Image Variants

Objective

Create a system for storing multiple image size variants for e-commerce products. Different sizes optimize display at various screen sizes and improve page load performance.

Step 1: Create Products Table

Create a base products table.

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

Step 2: Create Product Images Table

Create a table for storing image variants.

CREATE TABLE product_images (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    image_type VARCHAR(20) NOT NULL,
    thumbnail IMAGE(JPEG),
    small IMAGE(JPEG),
    medium IMAGE(JPEG),
    large IMAGE(JPEG),
    original IMAGE(JPEG),
    alt_text VARCHAR(255),
    sort_order INTEGER DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Step 3: Create Image Sizes Reference

Define standard image sizes.

CREATE TABLE image_size_config (
    size_name VARCHAR(20) PRIMARY KEY,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    quality INTEGER DEFAULT 85,
    use_case VARCHAR(100)
);

INSERT INTO image_size_config (size_name, width, height, quality, use_case) VALUES
    ('thumbnail', 100, 100, 70, 'Cart and mini-cart display'),
    ('small', 250, 250, 80, 'Product listing grid'),
    ('medium', 500, 500, 85, 'Product card hover'),
    ('large', 800, 800, 90, 'Product detail page'),
    ('original', 1500, 1500, 95, 'Zoom and download');

Step 4: Insert Sample Products

Add sample products.

INSERT INTO products (product_id, sku, name, description, price, category) VALUES
    (1, 'LAPTOP-001', 'Pro Laptop 15"', 'High-performance laptop for professionals', 1299.99, 'Electronics'),
    (2, 'HEADPHONE-001', 'Wireless Headphones', 'Premium noise-canceling headphones', 249.99, 'Electronics'),
    (3, 'SHIRT-001', 'Cotton T-Shirt', 'Comfortable 100% cotton t-shirt', 29.99, 'Apparel'),
    (4, 'WATCH-001', 'Smart Watch', 'Fitness tracking smart watch', 399.99, 'Electronics'),
    (5, 'BAG-001', 'Leather Backpack', 'Genuine leather travel backpack', 149.99, 'Accessories');

Step 5: Insert Product Images

Add image records for products.

INSERT INTO product_images (id, product_id, image_type, alt_text, sort_order, is_primary) VALUES
    (1, 1, 'front', 'Pro Laptop front view', 1, TRUE),
    (2, 1, 'angle', 'Pro Laptop angle view', 2, FALSE),
    (3, 1, 'back', 'Pro Laptop back ports', 3, FALSE),
    (4, 2, 'front', 'Wireless Headphones front', 1, TRUE),
    (5, 2, 'side', 'Wireless Headphones side', 2, FALSE),
    (6, 3, 'front', 'Cotton T-Shirt front', 1, TRUE),
    (7, 3, 'back', 'Cotton T-Shirt back', 2, FALSE),
    (8, 4, 'front', 'Smart Watch display', 1, TRUE),
    (9, 5, 'front', 'Leather Backpack front', 1, TRUE),
    (10, 5, 'open', 'Leather Backpack open', 2, FALSE);

Step 6: Query Products with Primary Images

Get products with their primary image.

SELECT
    p.sku,
    p.name,
    p.price,
    pi.alt_text as primary_image_alt,
    pi.image_type
FROM products p
LEFT JOIN product_images pi ON p.product_id = pi.product_id AND pi.is_primary = TRUE
WHERE p.is_active = TRUE
ORDER BY p.name;

Step 7: Get All Images for a Product

Retrieve all image variants for a product.

SELECT
    pi.image_type,
    pi.alt_text,
    pi.sort_order,
    pi.is_primary
FROM product_images pi
WHERE pi.product_id = 1
ORDER BY pi.sort_order;

Step 8: Products with Image Counts

Count images per product.

SELECT
    p.sku,
    p.name,
    COUNT(pi.id) as total_images,
    MAX(CASE WHEN pi.is_primary THEN 1 ELSE 0 END) as has_primary
FROM products p
LEFT JOIN product_images pi ON p.product_id = pi.product_id
GROUP BY p.product_id, p.sku, p.name
ORDER BY total_images DESC;

Step 9: Products Missing Images

Find products without any images.

SELECT
    p.product_id,
    p.sku,
    p.name,
    p.category
FROM products p
LEFT JOIN product_images pi ON p.product_id = pi.product_id
WHERE pi.id IS NULL
  AND p.is_active = TRUE;

Step 10: Products Without Primary Image

Find products that need a primary image set.

SELECT
    p.sku,
    p.name,
    COUNT(pi.id) as image_count
FROM products p
INNER JOIN product_images pi ON p.product_id = pi.product_id
LEFT JOIN product_images pri ON p.product_id = pri.product_id AND pri.is_primary = TRUE
WHERE pri.id IS NULL
GROUP BY p.product_id, p.sku, p.name;

Step 11: Category Image Statistics

Analyze image coverage by category.

SELECT
    p.category,
    COUNT(DISTINCT p.product_id) as product_count,
    COUNT(pi.id) as total_images,
    AVG(image_counts.img_count) as avg_images_per_product
FROM products p
LEFT JOIN product_images pi ON p.product_id = pi.product_id
LEFT JOIN (
    SELECT product_id, COUNT(*) as img_count
    FROM product_images
    GROUP BY product_id
) image_counts ON p.product_id = image_counts.product_id
GROUP BY p.category
ORDER BY product_count DESC;

Step 12: Set Primary Image

Update to set a new primary image.

-- Remove existing primary
UPDATE product_images
SET is_primary = FALSE
WHERE product_id = 1;

-- Set new primary
UPDATE product_images
SET is_primary = TRUE
WHERE id = 2;

-- Verify change
SELECT id, image_type, is_primary
FROM product_images
WHERE product_id = 1;

Cleanup (Optional)

DROP TABLE IF EXISTS product_images;
DROP TABLE IF EXISTS image_size_config;
DROP TABLE IF EXISTS products;

Expected Outcomes

• Multiple image sizes stored per product
• Primary image designation works
• Image ordering maintained
• Missing image detection functional
• Category-level statistics available

Standard E-commerce Image Sizes

Key Concepts Learned

• Multiple image variants per product
• Primary image designation
• Sort order for galleries
• Coverage analysis queries
• Foreign key relationships