Product Comparison Images

Objective

Create a system for managing product comparison images and data. This helps customers compare products side-by-side and make informed purchasing decisions.

Step 1: Create Products Table

Create a table for comparable products.

CREATE TABLE comparable_products (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100),
    price DECIMAL(10, 2),
    main_image IMAGE(JPEG),
    comparison_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Comparisons Table

Store comparison groups.

CREATE TABLE product_comparisons (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    comparison_image IMAGE(JPEG),
    product_count INTEGER DEFAULT 2,
    view_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_published BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create Comparison Items Table

Link products to comparisons.

CREATE TABLE comparison_items (
    id INTEGER PRIMARY KEY,
    comparison_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    position INTEGER NOT NULL,
    highlight_text VARCHAR(255),
    is_recommended BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (comparison_id) REFERENCES product_comparisons(id),
    FOREIGN KEY (product_id) REFERENCES comparable_products(id)
);

Step 4: Create Comparison Features Table

Store feature comparisons.

CREATE TABLE comparison_features (
    id INTEGER PRIMARY KEY,
    comparison_id INTEGER NOT NULL,
    feature_name VARCHAR(100) NOT NULL,
    feature_category VARCHAR(50),
    sort_order INTEGER,
    FOREIGN KEY (comparison_id) REFERENCES product_comparisons(id)
);

Step 5: Create Feature Values Table

Store product feature values.

CREATE TABLE feature_values (
    id INTEGER PRIMARY KEY,
    comparison_id INTEGER NOT NULL,
    feature_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    value VARCHAR(255),
    is_winner BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (comparison_id) REFERENCES product_comparisons(id),
    FOREIGN KEY (feature_id) REFERENCES comparison_features(id),
    FOREIGN KEY (product_id) REFERENCES comparable_products(id)
);

Step 6: Insert Sample Products

Add products for comparison.

INSERT INTO comparable_products (id, sku, name, category, subcategory, brand, price) VALUES
    (1, 'LAPTOP-PRO-15', 'ProBook Pro 15', 'Electronics', 'Laptops', 'TechBrand', 1499.99),
    (2, 'LAPTOP-AIR-15', 'ProBook Air 15', 'Electronics', 'Laptops', 'TechBrand', 1299.99),
    (3, 'LAPTOP-STD-15', 'ProBook Standard 15', 'Electronics', 'Laptops', 'TechBrand', 999.99),
    (4, 'PHONE-PRO-MAX', 'SmartPhone Pro Max', 'Electronics', 'Phones', 'MobileMax', 1199.99),
    (5, 'PHONE-PRO', 'SmartPhone Pro', 'Electronics', 'Phones', 'MobileMax', 999.99),
    (6, 'PHONE-STD', 'SmartPhone Standard', 'Electronics', 'Phones', 'MobileMax', 699.99);

Step 7: Insert Comparisons

Add product comparisons.

INSERT INTO product_comparisons (id, title, description, category, product_count, view_count, is_featured, created_by) VALUES
    (1, 'ProBook Laptop Comparison', 'Compare all ProBook laptop models', 'Laptops', 3, 15000, TRUE, 'product_team'),
    (2, 'SmartPhone Line Comparison', 'Compare SmartPhone models', 'Phones', 3, 22000, TRUE, 'product_team'),
    (3, 'Pro vs Standard Laptop', 'Which laptop is right for you?', 'Laptops', 2, 8500, FALSE, 'product_team');

Step 8: Link Products to Comparisons

Add comparison items.

INSERT INTO comparison_items (id, comparison_id, product_id, position, highlight_text, is_recommended) VALUES
    -- Laptop comparison
    (1, 1, 1, 1, 'Best Performance', TRUE),
    (2, 1, 2, 2, 'Best Value', FALSE),
    (3, 1, 3, 3, 'Budget Friendly', FALSE),
    -- Phone comparison
    (4, 2, 4, 1, 'Ultimate Experience', TRUE),
    (5, 2, 5, 2, 'Great Balance', FALSE),
    (6, 2, 6, 3, 'Essential Features', FALSE),
    -- Pro vs Standard
    (7, 3, 1, 1, 'For Professionals', TRUE),
    (8, 3, 3, 2, 'For Everyday Use', FALSE);

Step 9: Insert Comparison Features

Define features to compare.

INSERT INTO comparison_features (id, comparison_id, feature_name, feature_category, sort_order) VALUES
    -- Laptop features
    (1, 1, 'Processor', 'Performance', 1),
    (2, 1, 'RAM', 'Performance', 2),
    (3, 1, 'Storage', 'Storage', 3),
    (4, 1, 'Display', 'Display', 4),
    (5, 1, 'Battery Life', 'Battery', 5),
    (6, 1, 'Weight', 'Portability', 6),
    (7, 1, 'Price', 'Value', 7),
    -- Phone features
    (8, 2, 'Screen Size', 'Display', 1),
    (9, 2, 'Camera', 'Camera', 2),
    (10, 2, 'Battery', 'Battery', 3),
    (11, 2, 'Storage', 'Storage', 4),
    (12, 2, 'Price', 'Value', 5);

Step 10: Insert Feature Values

Add product feature values.

INSERT INTO feature_values (id, comparison_id, feature_id, product_id, value, is_winner) VALUES
    -- Laptop Pro features
    (1, 1, 1, 1, 'Intel i9 12th Gen', TRUE),
    (2, 1, 2, 1, '32GB DDR5', TRUE),
    (3, 1, 3, 1, '1TB SSD', TRUE),
    (4, 1, 4, 1, '15.6" 4K OLED', TRUE),
    (5, 1, 5, 1, '10 hours', FALSE),
    (6, 1, 6, 1, '2.1 kg', FALSE),
    (7, 1, 7, 1, '$1,499.99', FALSE),
    -- Laptop Air features
    (8, 1, 1, 2, 'Intel i7 12th Gen', FALSE),
    (9, 1, 2, 2, '16GB DDR5', FALSE),
    (10, 1, 3, 2, '512GB SSD', FALSE),
    (11, 1, 4, 2, '15.6" FHD IPS', FALSE),
    (12, 1, 5, 2, '12 hours', TRUE),
    (13, 1, 6, 2, '1.5 kg', TRUE),
    (14, 1, 7, 2, '$1,299.99', FALSE),
    -- Laptop Standard features
    (15, 1, 1, 3, 'Intel i5 12th Gen', FALSE),
    (16, 1, 2, 3, '8GB DDR4', FALSE),
    (17, 1, 3, 3, '256GB SSD', FALSE),
    (18, 1, 4, 3, '15.6" FHD', FALSE),
    (19, 1, 5, 3, '8 hours', FALSE),
    (20, 1, 6, 3, '1.8 kg', FALSE),
    (21, 1, 7, 3, '$999.99', TRUE);

Step 11: View Full Comparison

Get comparison with all products and features.

SELECT
    cf.feature_name,
    cp1.name as product_1,
    fv1.value as value_1,
    fv1.is_winner as winner_1,
    cp2.name as product_2,
    fv2.value as value_2,
    fv2.is_winner as winner_2
FROM comparison_features cf
LEFT JOIN feature_values fv1 ON cf.id = fv1.feature_id AND fv1.product_id = 1
LEFT JOIN feature_values fv2 ON cf.id = fv2.feature_id AND fv2.product_id = 2
LEFT JOIN comparable_products cp1 ON fv1.product_id = cp1.id
LEFT JOIN comparable_products cp2 ON fv2.product_id = cp2.id
WHERE cf.comparison_id = 1
ORDER BY cf.sort_order;

Step 12: Popular Comparisons

Get most viewed comparisons.

SELECT
    pc.title,
    pc.category,
    pc.product_count,
    pc.view_count,
    pc.is_featured
FROM product_comparisons pc
WHERE pc.is_published = TRUE
ORDER BY pc.view_count DESC
LIMIT 10;

Cleanup (Optional)

DROP TABLE IF EXISTS feature_values;
DROP TABLE IF EXISTS comparison_features;
DROP TABLE IF EXISTS comparison_items;
DROP TABLE IF EXISTS product_comparisons;
DROP TABLE IF EXISTS comparable_products;

Expected Outcomes

• Products linked in comparisons
• Features compared side-by-side
• Winners highlighted
• View counts tracked
• Recommendations marked

Feature Categories

Key Concepts Learned

• Product comparison tables
• Feature-value mapping
• Winner highlighting
• Recommendation flags
• Comparison analytics