# Product Catalog System Low-Level Design

Source: https://www.techinterview.org/post/3233467806/lld-product-catalog/
Updated: 2026-04-18 · techinterview.org

## What is a Product Catalog System?

A product catalog stores and serves product data: names, descriptions, images, prices, variants, and attributes. Amazon has 350 million products; Shopify serves millions of merchants. The catalog must support fast reads (product page, search), flexible attribute schemas (a laptop has different fields than a dress), variant management (size x color combinations), and real-time price and availability updates without full cache invalidation.

## Requirements

  - Store products with flexible attributes (electronics vs apparel have different fields)

  - Variant support: size, color, material combinations with per-variant pricing and stock

  - Real-time price and availability updates (<5 second propagation)

  - Product page load: <100ms including images, price, variants, reviews count

  - Full-text search across name, description, attributes

  - 100M products, 10K price updates/second during sales events

## Data Model


```
Product(product_id UUID, merchant_id UUID, name VARCHAR,
        description TEXT, category_id UUID,
        status ENUM(ACTIVE, INACTIVE, DRAFT),
        created_at, updated_at)

ProductAttribute(product_id UUID, key VARCHAR, value VARCHAR,
                 PRIMARY KEY (product_id, key))
-- EAV: flexible attributes without schema changes
-- 'brand'='Apple', 'processor'='M3', 'screen_size'='14'

ProductVariant(variant_id UUID, product_id UUID, sku VARCHAR UNIQUE,
               options JSONB,          -- {'size': 'M', 'color': 'blue'}
               price_cents INT,
               compare_at_price_cents INT,
               inventory_quantity INT DEFAULT 0)

ProductImage(image_id UUID, product_id UUID, variant_id UUID,
             url VARCHAR, alt_text VARCHAR, position INT)
```


## Flexible Attributes with EAV

Entity-Attribute-Value (EAV) allows arbitrary product attributes without schema changes. A laptop gets 'processor', 'ram_gb', 'storage_type'; a dress gets 'material', 'neckline', 'sleeve_length'. These cannot be predefined as columns without hundreds of nullable columns.


```
-- Index for attribute-based filtering
CREATE INDEX idx_attr_lookup ON ProductAttribute(key, value, product_id);

-- Filter products by attribute: all Apple laptops
SELECT DISTINCT p.product_id FROM Product p
JOIN ProductAttribute a1 ON p.product_id = a1.product_id
JOIN ProductAttribute a2 ON p.product_id = a2.product_id
WHERE a1.key='brand' AND a1.value='Apple'
  AND a2.key='category' AND a2.value='laptop';

-- Full product detail (3 queries, no N+1)
def get_product_detail(product_id):
    product    = db.get_product(product_id)
    attributes = db.get_attributes(product_id)
    variants   = db.get_variants(product_id)
    images     = db.get_images(product_id)
    return {**product, 'attributes': attributes,
            'variants': variants, 'images': images}
```


## Price and Availability Updates


```
def update_variant_price(variant_id, new_price_cents):
    db.execute('''
        UPDATE ProductVariant SET price_cents=:price, updated_at=NOW()
        WHERE variant_id=:vid
    ''', price=new_price_cents, vid=variant_id)

    # Invalidate product page cache
    product_id = get_product_id_for_variant(variant_id)
    redis.delete(f'product:{product_id}')

    # Publish event for search index reindex
    kafka.produce('catalog-updates', {
        'type': 'price_update',
        'variant_id': str(variant_id),
        'product_id': str(product_id)
    })
```


## Product Page Caching


```
def get_product_page(product_id):
    key = f'product:{product_id}'
    cached = redis.get(key)
    if cached:
        return json.loads(cached)

    product = get_product_detail(product_id)
    redis.setex(key, 300, json.dumps(product))  # TTL 5 minutes
    return product
```

Event-driven invalidation: Kafka consumer listens for catalog-updates events and calls redis.delete for affected product keys. This ensures price changes propagate within seconds rather than waiting for TTL expiry.

## Key Design Decisions

  - EAV for attributes — flexible schema; new attribute types need no migrations

  - Variants as separate rows — each size/color combination has its own SKU, price, and inventory count

  - Redis cache for product pages — 3-4 DB queries per product; cache keeps origin load manageable

  - Event-driven cache invalidation — Kafka price-update event triggers immediate cache delete

  - Batch attribute/variant/image queries — avoids N+1; 3 queries total for full product detail regardless of variant count

Product catalog system design is a key topic in [Amazon system design interview questions](https://www.techinterview.org/post/3233460275/scale-ai-interview-guide-2026-data-infrastructure-rlhf-pipelines-and-ml-engineering/).

Product catalog and variant management design is covered in [Shopify system design interview preparation](https://www.techinterview.org/post/3233459924/shopify-interview/).

Product catalog and search indexing design is discussed in [Google system design interview guide](https://www.techinterview.org/post/3233460274/netflix-interview-guide-2026-streaming-architecture-recommendation-systems-and-engineering-excellence/).

See also: [Airbnb Interview Guide 2026: Search Systems, Trust and Safety, and Full-Stack Engineering](https://www.techinterview.org/post/3233460279/airbnb-interview-guide-2026-search-systems-trust-and-safety-and-full-stack-engineering/)