User-Uploaded Product Reviews with Images

Objective

Create a customer review system with photo uploads and ratings. This enables social proof, authentic product feedback, and improved purchasing decisions.

Step 1: Create Products Table

Create a table for reviewed products.

CREATE TABLE reviewed_products (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    avg_rating DECIMAL(3, 2) DEFAULT 0,
    review_count INTEGER DEFAULT 0,
    photo_review_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Reviews Table

Create a table for customer reviews.

CREATE TABLE product_reviews (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    customer_id VARCHAR(100) NOT NULL,
    customer_name VARCHAR(100),
    rating INTEGER NOT NULL,
    title VARCHAR(255),
    review_text TEXT,
    has_photos BOOLEAN DEFAULT FALSE,
    photo_count INTEGER DEFAULT 0,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    helpful_count INTEGER DEFAULT 0,
    is_approved BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES reviewed_products(id)
);

Step 3: Create Review Photos Table

Store customer-uploaded photos.

CREATE TABLE review_photos (
    id INTEGER PRIMARY KEY,
    review_id INTEGER NOT NULL,
    photo IMAGE(JPEG),
    caption TEXT,
    photo_order INTEGER DEFAULT 1,
    width INTEGER,
    height INTEGER,
    file_size INTEGER,
    is_approved BOOLEAN DEFAULT TRUE,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES product_reviews(id)
);

Step 4: Insert Sample Products

Add products for reviews.

INSERT INTO reviewed_products (id, sku, name, category) VALUES
    (1, 'SHOES-RUN-001', 'ProRunner Sports Shoes', 'Footwear'),
    (2, 'BAG-BACK-001', 'TravelMax Backpack', 'Bags'),
    (3, 'WATCH-FIT-001', 'FitTrack Smart Watch', 'Electronics'),
    (4, 'JACKET-OUT-001', 'AllWeather Jacket', 'Clothing'),
    (5, 'CAMERA-ACT-001', 'ActionCam Pro', 'Electronics');

Step 5: Insert Customer Reviews

Add sample reviews.

INSERT INTO product_reviews (id, product_id, customer_id, customer_name, rating, title, review_text, has_photos, photo_count, is_verified_purchase, helpful_count, is_featured, review_date) VALUES
    -- Shoes reviews
    (1, 1, 'cust_001', 'Mike R.', 5, 'Best running shoes ever!', 'These shoes are incredibly comfortable. Ive run 3 marathons in them with no issues. Great cushioning and support.', TRUE, 3, TRUE, 45, TRUE, '2024-01-10 14:30:00'),
    (2, 1, 'cust_002', 'Sarah L.', 4, 'Great shoes, runs slightly small', 'Love the quality and comfort. Order half size up as they run a bit small. Highly recommend!', TRUE, 2, TRUE, 28, FALSE, '2024-01-12 09:15:00'),
    (3, 1, 'cust_003', 'John D.', 3, 'Decent but expected more', 'Quality is okay but not exceptional. Comfortable for daily use but wouldnt recommend for serious running.', FALSE, 0, TRUE, 12, FALSE, '2024-01-15 16:45:00'),
    -- Backpack reviews
    (4, 2, 'cust_004', 'Emily K.', 5, 'Perfect travel companion', 'Used this for a 3-week Europe trip. Fits carry-on size, has tons of pockets, and is waterproof!', TRUE, 4, TRUE, 67, TRUE, '2024-01-08 11:20:00'),
    (5, 2, 'cust_005', 'Chris M.', 5, 'Durable and spacious', 'Bought for hiking and its perfect. Great back support and laptop compartment is super useful.', TRUE, 2, TRUE, 34, FALSE, '2024-01-18 13:00:00'),
    -- Watch reviews
    (6, 3, 'cust_006', 'Amanda P.', 4, 'Great fitness features', 'Accurate heart rate monitoring and sleep tracking. Battery lasts about 5 days. App could be better.', TRUE, 1, TRUE, 23, FALSE, '2024-01-20 10:30:00'),
    (7, 3, 'cust_007', 'Tom B.', 5, 'Exceeded expectations', 'Best smartwatch at this price point. All the features I need for workouts and daily use.', TRUE, 3, TRUE, 41, TRUE, '2024-01-22 15:45:00'),
    -- Camera reviews
    (8, 5, 'cust_008', 'Lisa J.', 5, 'Amazing video quality', 'The 4K footage is stunning. Waterproof and compact. Perfect for my scuba diving trips.', TRUE, 5, TRUE, 89, TRUE, '2024-01-05 08:00:00');

Step 6: Insert Review Photos

Add customer photos.

INSERT INTO review_photos (id, review_id, caption, photo_order, width, height, file_size) VALUES
    -- Shoes review 1 photos
    (1, 1, 'After 500 miles still going strong', 1, 1200, 900, 245000),
    (2, 1, 'Great cushioning visible here', 2, 1200, 900, 198000),
    (3, 1, 'Sole wear after marathon training', 3, 1200, 900, 215000),
    -- Shoes review 2 photos
    (4, 2, 'Beautiful color in person', 1, 800, 600, 125000),
    (5, 2, 'Size comparison with old shoes', 2, 800, 600, 134000),
    -- Backpack review photos
    (6, 4, 'Packed for Europe trip', 1, 1000, 1000, 312000),
    (7, 4, 'All the compartments open', 2, 1200, 800, 287000),
    (8, 4, 'Waterproof test in rain', 3, 800, 1200, 198000),
    (9, 4, 'Fits perfectly as carry-on', 4, 900, 1200, 225000),
    -- Watch review photos
    (10, 6, 'Watch face options', 1, 600, 600, 95000),
    (11, 7, 'Fitness tracking display', 1, 800, 800, 145000),
    (12, 7, 'On my wrist', 2, 600, 800, 112000),
    (13, 7, 'Compared to previous watch', 3, 1000, 600, 178000),
    -- Camera review photos
    (14, 8, 'Camera size comparison', 1, 1200, 800, 215000),
    (15, 8, 'Underwater footage screenshot', 2, 1920, 1080, 425000),
    (16, 8, 'Mounting accessories', 3, 1000, 1000, 265000),
    (17, 8, '4K video frame sample', 4, 1920, 1080, 398000),
    (18, 8, 'Night mode sample', 5, 1920, 1080, 312000);

Step 7: Update Product Statistics

Calculate review statistics.

UPDATE reviewed_products
SET avg_rating = (
    SELECT AVG(rating) FROM product_reviews
    WHERE product_reviews.product_id = reviewed_products.id
      AND product_reviews.is_approved = TRUE
),
review_count = (
    SELECT COUNT(*) FROM product_reviews
    WHERE product_reviews.product_id = reviewed_products.id
      AND product_reviews.is_approved = TRUE
),
photo_review_count = (
    SELECT COUNT(*) FROM product_reviews
    WHERE product_reviews.product_id = reviewed_products.id
      AND product_reviews.is_approved = TRUE
      AND product_reviews.has_photos = TRUE
);

SELECT name, avg_rating, review_count, photo_review_count FROM reviewed_products;

Step 8: Get Product Reviews

List reviews for a product.

SELECT
    pr.customer_name,
    pr.rating,
    pr.title,
    pr.review_text,
    pr.photo_count,
    pr.is_verified_purchase,
    pr.helpful_count,
    pr.review_date
FROM product_reviews pr
WHERE pr.product_id = 1
  AND pr.is_approved = TRUE
ORDER BY pr.helpful_count DESC;

Step 9: Get Review with Photos

View review and its photos.

SELECT
    pr.customer_name,
    pr.title,
    pr.review_text,
    rp.caption,
    rp.photo_order,
    rp.width || 'x' || rp.height as dimensions
FROM product_reviews pr
INNER JOIN review_photos rp ON pr.id = rp.review_id
WHERE pr.id = 1
ORDER BY rp.photo_order;

Step 10: Photo Reviews Gallery

Get all photo reviews for a product.

SELECT
    pr.customer_name,
    pr.rating,
    pr.title,
    COUNT(rp.id) as photos,
    pr.review_date
FROM product_reviews pr
INNER JOIN review_photos rp ON pr.id = rp.review_id
WHERE pr.product_id = 1
  AND pr.is_approved = TRUE
  AND rp.is_approved = TRUE
GROUP BY pr.id, pr.customer_name, pr.rating, pr.title, pr.review_date
ORDER BY pr.review_date DESC;

Step 11: Rating Distribution

Get rating breakdown for a product.

SELECT
    rating,
    COUNT(*) as count,
    COUNT(CASE WHEN has_photos THEN 1 END) as with_photos
FROM product_reviews
WHERE product_id = 1
  AND is_approved = TRUE
GROUP BY rating
ORDER BY rating DESC;

Step 12: Top Helpful Reviews

Get most helpful reviews across all products.

SELECT
    rp.name as product,
    pr.customer_name,
    pr.rating,
    pr.title,
    pr.helpful_count,
    pr.photo_count
FROM product_reviews pr
INNER JOIN reviewed_products rp ON pr.product_id = rp.id
WHERE pr.is_approved = TRUE
ORDER BY pr.helpful_count DESC
LIMIT 10;

Cleanup (Optional)

DROP TABLE IF EXISTS review_photos;
DROP TABLE IF EXISTS product_reviews;
DROP TABLE IF EXISTS reviewed_products;

Expected Outcomes

• Customer reviews stored
• Photos linked to reviews
• Ratings calculated
• Helpful votes tracked
• Featured reviews identified

Rating Guidelines

Key Concepts Learned

• User-generated content management
• Review photo handling
• Rating aggregation
• Verified purchase tracking
• Helpfulness voting