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');