Size Guide with Images

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

Step 10: Insert Measurements

Add size measurements.

INSERT INTO size_measurements (id, size_id, measurement_name, min_value, max_value, unit) VALUES
    -- Mens Tops measurements
    (1, 1, 'Chest', 32, 34, 'inches'),
    (2, 1, 'Neck', 13.5, 14, 'inches'),
    (3, 2, 'Chest', 35, 37, 'inches'),
    (4, 2, 'Neck', 14, 14.5, 'inches'),
    (5, 3, 'Chest', 38, 40, 'inches'),
    (6, 3, 'Neck', 15, 15.5, 'inches'),
    (7, 4, 'Chest', 41, 43, 'inches'),
    (8, 4, 'Neck', 16, 16.5, 'inches'),
    (9, 5, 'Chest', 44, 46, 'inches'),
    (10, 5, 'Neck', 17, 17.5, 'inches'),
    -- Mens Sneakers measurements (foot length)
    (11, 12, 'Foot Length', 9.625, 9.75, 'inches'),
    (12, 13, 'Foot Length', 9.9375, 10.125, 'inches'),
    (13, 14, 'Foot Length', 10.25, 10.4375, 'inches'),
    (14, 15, 'Foot Length', 10.5625, 10.75, 'inches'),
    (15, 16, 'Foot Length', 10.875, 11.0625, 'inches'),
    (16, 17, 'Foot Length', 11.1875, 11.375, 'inches');

Step 11: Insert Measurement Guides

Add how-to-measure images.

INSERT INTO measurement_guides (id, category_id, measurement_name, instructions, tip_text, sort_order) VALUES
    -- Clothing measurements
    (1, 4, 'Chest', 'Measure around the fullest part of your chest, keeping the tape horizontal.', 'Keep arms relaxed at sides', 1),
    (2, 4, 'Neck', 'Measure around the base of your neck where a collar would sit.', 'Allow two fingers of room', 2),
    (3, 4, 'Sleeve', 'Measure from center back of neck, across shoulder, down to wrist.', 'Bend elbow slightly', 3),
    (4, 5, 'Waist', 'Measure around your natural waistline, above hip bones.', 'Dont pull tape too tight', 1),
    (5, 5, 'Hips', 'Measure around fullest part of hips and buttocks.', 'Stand with feet together', 2),
    (6, 5, 'Inseam', 'Measure from crotch to bottom of ankle bone.', 'Wear shoes youll pair with', 3),
    -- Footwear measurements
    (7, 2, 'Foot Length', 'Stand on paper and trace your foot. Measure from heel to longest toe.', 'Measure in afternoon when feet are largest', 1),
    (8, 2, 'Foot Width', 'Measure the widest part of your traced foot outline.', 'Use this for wide/narrow options', 2);

Step 12: Insert Fit Recommendations

Add fit advice.

INSERT INTO fit_recommendations (id, chart_id, fit_type, description, best_for) VALUES
    (1, 1, 'Slim Fit', 'Fitted through chest and waist with tapered sides', 'Athletic builds, modern look'),
    (2, 1, 'Regular Fit', 'Classic fit with standard room through body', 'Everyday comfort, most body types'),
    (3, 1, 'Relaxed Fit', 'Generous room throughout for maximum comfort', 'Casual wear, layering'),
    (4, 5, 'True to Size', 'Order your normal shoe size', 'Standard foot width'),
    (5, 5, 'Half Size Up', 'Consider ordering half size larger', 'Wide feet, thick socks'),
    (6, 5, 'Half Size Down', 'Consider ordering half size smaller', 'Narrow feet, no-show socks');

Step 13: Get Size Chart with Measurements

Display complete size guide.

SELECT
    sd.size_code,
    sd.size_name,
    sm.measurement_name,
    sm.min_value || '-' || sm.max_value || ' ' || sm.unit as range
FROM size_definitions sd
INNER JOIN size_measurements sm ON sd.id = sm.size_id
WHERE sd.chart_id = 1
ORDER BY sd.sort_order, sm.measurement_name;

Step 14: Get Measurement Guide for Category

Retrieve how-to-measure instructions.

SELECT
    mg.measurement_name,
    mg.instructions,
    mg.tip_text
FROM measurement_guides mg
WHERE mg.category_id = 4
ORDER BY mg.sort_order;

Cleanup (Optional)

DROP TABLE IF EXISTS fit_recommendations;
DROP TABLE IF EXISTS measurement_guides;
DROP TABLE IF EXISTS size_measurements;
DROP TABLE IF EXISTS size_definitions;
DROP TABLE IF EXISTS size_charts;
DROP TABLE IF EXISTS sizing_categories;

Expected Outcomes

• Size charts organized
• Measurements defined
• Guide images stored
• Fit recommendations available
• Multi-region support

Measurement Types

Key Concepts Learned

• Size chart structure
• Measurement ranges
• Visual measurement guides
• Fit type recommendations
• Regional size variations