Building a Denim Collection API: A Practical Guide to Handling Product Variants

If you've ever worked with e-commerce data, you know that "a pair of jeans" is never just one product. A single style might come in 5 washes, 8 sizes, and 3 inseam lengths. That's 120 potential SKUs. Handling this correctly in an API can be tricky, so let me share a pattern I've used for structuring product variants.

The core problem is balancing flexibility with performance. You want customers to filter by size, color, and fit without making dozens of API calls.

Here's a simple but effective approach using a normalized database schema with a flat query layer:

-- Products table (the "parent")
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  base_price DECIMAL(10,2),
  category TEXT
);

-- Variants table (the actual sellable items)
CREATE TABLE variants (
  id UUID PRIMARY KEY,
  product_id UUID REFERENCES products(id),
  sku TEXT UNIQUE NOT NULL,
  size TEXT,
  color TEXT,
  wash TEXT,
  inseam TEXT,
  price DECIMAL(10,2),  -- can override base price
  stock_quantity INT,
  image_url TEXT
);

The key insight? Keep the product metadata (description, care instructions, brand story) in the products table, but put all the sellable attributes in variants. This lets you run queries like:

-- Find all size 28 jeans in "mid wash" under $80
SELECT p.name, v.color, v.wash, v.price, v.stock_quantity
FROM products p
JOIN variants v ON p.id = v.product_id
WHERE p.category = 'women-jeans'
  AND v.size = '28'
  AND v.wash LIKE '%mid%'
  AND v.price < 80
  AND v.stock_quantity > 0
ORDER BY v.price;

For the frontend, I usually return a flattened structure:

{
  "product": {
    "id": "abc-123",
    "name": "Classic Straight Leg Jean",
    "description": "High-rise fit in stretch denim...",
    "availableSizes": ["24","25","26","27","28"],
    "availableColors": [
      {
        "name": "Indigo Wash",
        "image": "/images/indigo.jpg",
        "variants": [
          {"size":"27","sku":"JEAN-IND-27","stock":5,"price":69.99}
        ]
      }
    ]
  }
}

The real magic happens when you add a materialized view for common queries. If your store sells thousands of denim styles (like exploring a collection of women's jeans), this pattern scales nicely without over-engineering.

What's your approach? I'm curious how others handle variant-heavy product data.