Objective
Build a comprehensive size guide system with measurement images and fit recommendations. This helps customers choose the right size and reduces returns.
Step 1: Create Product Categories Table
Define categories with sizing needs.
CREATE TABLE sizing_categories (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INTEGER,
has_size_guide BOOLEAN DEFAULT TRUE,
measurement_type VARCHAR(50),
FOREIGN KEY (parent_id) REFERENCES sizing_categories(id)
);
Step 2: Create Size Charts Table
Store size definitions.
CREATE TABLE size_charts (
id INTEGER PRIMARY KEY,
category_id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
region VARCHAR(20) DEFAULT 'US',
gender VARCHAR(20),
chart_image IMAGE(PNG),
description TEXT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES sizing_categories(id)
);
Step 3: Create Size Definitions Table
Define individual sizes.
CREATE TABLE size_definitions (
id INTEGER PRIMARY KEY,
chart_id INTEGER NOT NULL,
size_code VARCHAR(20) NOT NULL,
size_name VARCHAR(50),
sort_order INTEGER,
is_available BOOLEAN DEFAULT TRUE,
FOREIGN KEY (chart_id) REFERENCES size_charts(id)
);
Step 4: Create Measurements Table
Store size measurements.
CREATE TABLE size_measurements (
id INTEGER PRIMARY KEY,
size_id INTEGER NOT NULL,
measurement_name VARCHAR(50) NOT NULL,
min_value DECIMAL(6, 2),
max_value DECIMAL(6, 2),
unit VARCHAR(10) DEFAULT 'inches',
FOREIGN KEY (size_id) REFERENCES size_definitions(id)
);
Step 5: Create Measurement Guide Images
Store how-to-measure images.
CREATE TABLE measurement_guides (
id INTEGER PRIMARY KEY,
category_id INTEGER NOT NULL,
measurement_name VARCHAR(50) NOT NULL,
guide_image IMAGE(PNG),
instructions TEXT,
tip_text VARCHAR(255),
sort_order INTEGER,
FOREIGN KEY (category_id) REFERENCES sizing_categories(id)
);
Step 6: Create Fit Recommendations Table
Store fit advice.
CREATE TABLE fit_recommendations (
id INTEGER PRIMARY KEY,
chart_id INTEGER NOT NULL,
fit_type VARCHAR(50) NOT NULL,
description TEXT,
recommendation_image IMAGE(JPEG),
best_for TEXT,
FOREIGN KEY (chart_id) REFERENCES size_charts(id)
);
Step 7: Insert Sizing Categories
Add product categories.
INSERT INTO sizing_categories (id, name, parent_id, has_size_guide, measurement_type) VALUES
(1, 'Clothing', NULL, TRUE, 'body'),
(2, 'Footwear', NULL, TRUE, 'foot'),
(3, 'Accessories', NULL, TRUE, 'various'),
-- Clothing subcategories
(4, 'Tops', 1, TRUE, 'body'),
(5, 'Bottoms', 1, TRUE, 'body'),
(6, 'Dresses', 1, TRUE, 'body'),
(7, 'Outerwear', 1, TRUE, 'body'),
-- Footwear subcategories
(8, 'Sneakers', 2, TRUE, 'foot'),
(9, 'Boots', 2, TRUE, 'foot'),
(10, 'Sandals', 2, TRUE, 'foot'),
-- Accessories subcategories
(11, 'Hats', 3, TRUE, 'head'),
(12, 'Belts', 3, TRUE, 'waist'),
(13, 'Rings', 3, TRUE, 'finger');
Step 8: Insert Size Charts
Add size chart definitions.
INSERT INTO size_charts (id, category_id, name, region, gender, description) VALUES
(1, 4, 'Mens Tops US', 'US', 'mens', 'Standard US mens shirt and top sizing'),
(2, 4, 'Womens Tops US', 'US', 'womens', 'Standard US womens top sizing'),
(3, 5, 'Mens Bottoms US', 'US', 'mens', 'US mens pants and shorts sizing'),
(4, 5, 'Womens Bottoms US', 'US', 'womens', 'US womens pants and shorts sizing'),
(5, 8, 'Mens Sneakers US', 'US', 'mens', 'US mens sneaker sizing'),
(6, 8, 'Womens Sneakers US', 'US', 'womens', 'US womens sneaker sizing'),
(7, 11, 'Hat Sizes', 'US', 'unisex', 'Standard hat sizing'),
(8, 12, 'Belt Sizes', 'US', 'unisex', 'Belt length sizing');
Step 9: Insert Size Definitions
Add individual sizes.
INSERT INTO size_definitions (id, chart_id, size_code, size_name, sort_order) VALUES
-- Mens Tops
(1, 1, 'XS', 'Extra Small', 1),
(2, 1, 'S', 'Small', 2),
(3, 1, 'M', 'Medium', 3),
(4, 1, 'L', 'Large', 4),
(5, 1, 'XL', 'Extra Large', 5),
(6, 1, '2XL', '2X Large', 6),
-- Womens Tops
(7, 2, 'XS', 'Extra Small (0-2)', 1),
(8, 2, 'S', 'Small (4-6)', 2),
(9, 2, 'M', 'Medium (8-10)', 3),
(10, 2, 'L', 'Large (12-14)', 4),
(11, 2, 'XL', 'Extra Large (16-18)', 5),
-- Mens Sneakers
(12, 5, '7', 'US 7', 1),
(13, 5, '8', 'US 8', 2),
(14, 5, '9', 'US 9', 3),
(15, 5, '10', 'US 10', 4),
(16, 5, '11', 'US 11', 5),
(17, 5, '12', 'US 12', 6);