Catalog System Design

EAV vs Flat Table: The Real Decision

EAV (Entity-Attribute-Value) gives flexibility but costs performance. The decision framework:

Use EAV When:
├── Products have highly variable attributes
├── Different product types need different fields
├── Attribute set changes frequently
├── < 100K products
└── Admin UI flexibility is priority

Use Flat Catalog When:
├── Same attributes apply to most products
├── Search/browse performance is critical
├── > 100K products with similar structure
├── Can tolerate rebuild time for attribute changes
└── Elasticsearch is primary search engine

Hybrid Approach (Recommended for > 50K products):
├── EAV for admin/product management
├── Flat/Elasticsearch for frontend search
├── Async sync between EAV → flat/search index
└── Accept 5-30 second delay for new products to appear

Real Performance Impact

Product listing query (100 products, 50 attributes):

EAV (default Magento):
├── JOINs: 50+ tables (one per attribute)
├── Query time: 200-800ms
├── Memory: 50-100MB per query
└── Problem: Each attribute adds a JOIN

Flat catalog enabled:
├── JOINs: 0 (single table)
├── Query time: 5-20ms
├── Memory: 2-5MB per query
└── Trade-off: Must rebuild when attributes change

Elasticsearch:
├── JOINs: 0
├── Query time: 10-50ms
├── Memory: Minimal (search server)
└── Trade-off: Eventual consistency, indexing overhead

Real failure scenario: A store with 200K products and 300 attributes had EAV with 300 JOINs per product query. Page load time was 3-5 seconds. Fix: Enable flat catalog + Elasticsearch. Page load dropped to 300ms. But: Reindex time increased from 15 minutes to 2 hours. Solution: Schedule reindex during off-peak hours + use partial reindex for product updates.

Attribute Design Decisions

Attribute Count Impact:
├── 50 attributes: EAV query ~200ms, acceptable
├── 100 attributes: EAV query ~500ms, noticeable
├── 200 attributes: EAV query ~1s, problematic
├── 300+ attributes: EAV query ~2-5s, unacceptable

Attribute Type Impact:
├── Text/varchar: Stored in separate table, JOIN needed
├── Int/decimal: Stored in separate table, JOIN needed
├── Yes/no: Can be stored as int, minimal overhead
├── Dropdown: Source model lookup, extra JOIN
└── Multi-select: Index table, significant overhead

Best Practices:
├── Limit to 100 attributes per product type
├── Use text/varchar for searchable fields
├── Use int/decimal for filterable fields
├── Avoid multi-select for products > 10K
├── Use Elasticsearch for complex attribute queries
└── Cache attribute metadata (static per store)

Product Type Performance

Simple Product:
├── Fastest: No relationship lookups
├── Storage: 1 row in catalog_product_entity
└── Query: Single row fetch

Configurable Product:
├── Moderate: Must load children
├── Storage: Parent + N children rows
├── Query: Parent + child SELECT + stock check
└── Impact: Configurable with 50 variants = 51 rows

Bundle Product:
├── Slow: Multiple option lookups
├── Storage: Parent + options + selections
├── Query: Parent + options + products + stock
└── Impact: Price calculation requires all selections

Grouped Product:
├── Moderate: Collection of simples
├── Storage: Link table + N children
├── Query: Link table + child SELECTs
└── Impact: Each child has own stock/price

Real issue: Store had configurable products with 200 variants each. Product page load was 2s because it loaded all 200 variants. Fix: Lazy-load variants via AJAX, only load first 20 on initial page. Page load dropped to 400ms.

Category Tree Depth vs Performance

Depth Impact:
├── 3 levels: Standard, manageable
├── 4 levels: Common for large catalogs
├── 5+ levels: Performance issues begin
└── 10+ levels: Query complexity explodes

Path Storage:
├── Magento stores: 1/2/3/4 (slash-separated)
├── Max path length: 255 characters
├── Implication: Max ~20 levels (10 chars per ID)
└── Real limit: ~15 levels practical

Tree Query Performance:
├── Get category by ID: O(1) - direct lookup
├── Get children: O(n) where n = children count
├── Get full path: O(depth) - path parsing
├── Get category tree: O(n²) - recursive load
└── Cache: Category tree cache essential for > 100 categories

Category Product Assignment

Assignment Strategies:

Direct Assignment (Default):
├── catalog_category_product table
├── Fast: Direct lookup by category_id
├── Limitation: Product in many categories = many rows
└── Real: 10K products × 5 categories avg = 50K rows

Anchor Categories:
├── Show products from child categories
├── Uses 'is_anchor' flag
├── Performance: Must aggregate children
├── Impact: Category with 10 children = 10x query load
└── Fix: Pre-compute anchor product lists

Dynamic Categories (Rules-based):
├── Products assigned by attribute rules
├── Example: All products with price < $50
├── Performance: Rule evaluation on every request
├── Impact: Complex rules = slow category pages
└── Fix: Cache rule results, rebuild on product save

Real incident: A store used anchor categories for all 500 categories. Category page load was 2-3 seconds because each page aggregated products from 10-20 child categories. Fix: Disable anchoring for deep categories (> 3 levels), pre-compute product counts. Page load dropped to 300ms.

Category URL Key Conflicts

Conflict Scenario:
├── Category: /electronics/phones
├── Product: /electronics/phones
├── Result: 404 or wrong entity loaded

Resolution:
├── Magento prepends category path to product URL
├── Product URL becomes: /electronics/phones/iphone-15
├── Category URL stays: /electronics/phones
└── But: Can still conflict if product SKU = category URL key

Best Practice:
├── Use different URL key patterns
├── Category: /electronics/phones
├── Product: /iphone-15-pro-max
├── Avoid: /electronics/phones/samsung-galaxy (too similar)
└── Validate: URL key uniqueness across all entities

Category Cache Strategy

What to Cache:
├── Category tree structure (changes rarely)
├── Category product counts (changes on product save)
├── Category product lists (changes on assignment)
├── Category page HTML (changes on content update)
└── Category URLs (changes on URL key update)

Cache Invalidation:
├── Product save → invalidate category cache for product's categories
├── Category save → invalidate category tree + affected pages
├── Mass update → invalidate all category caches (acceptable)
└── Reindex → invalidate category + search caches

Real Issue: Cache invalidation was too aggressive. Saving a product invalidated all category caches (500 categories). Fix: Track which categories each product belongs to, only invalidate those. Cache hit ratio improved from 60% to 92%.

Price Calculation Order

Magento price calculation pipeline:

1. Base Price (catalog_product_entity_price)
2. Group Price (customer group discount)
3. Tier Price (quantity discount)
4. Special Price (time-limited sale)
5. Catalog Price Rules (attribute-based rules)
6. Tax Calculation
7. Final Price

Order matters:
├── Tier price applied AFTER group price
├── Special price overrides base price (not cumulative)
├── Catalog rules applied LAST (before tax)
└── Cart price rules applied at cart level (after catalog)

Real complexity: Customer in wholesale group (10% off) buying 100 units (tier: 20% off) during sale (special: 30% off).
Calculation: $100 × 0.9 (group) × 0.8 (tier) × 0.7 (special) = $50.40
Is this correct? Depends on business rules. Some stores want: $100 × 0.7 (best discount only) = $70.

Price Performance Issues

Problem: Price calculation on every page load
├── 100 products × 7 calculation steps = 700 operations
├── Each step: database query or cache lookup
├── Total: 200-500ms for category page
└── Impact: Slow category pages, poor UX

Solutions:

1. Price Cache (Recommended):
   ├── Calculate price on product save
   ├── Store in catalog_product_index_price
   ├── Frontend reads from index (fast)
   └── Trade-off: 5-30 second delay for price changes

2. Elasticsearch Price Index:
   ├── Index prices in search engine
   ├── Category page reads from ES (fast)
   ├── Filter/sort by price in ES (fast)
   └── Trade-off: Eventual consistency

3. Static Price Generation:
   ├── Generate static HTML for each product
   ├── Serve from CDN/Varnish
   ├── Fastest possible response
   └── Trade-off: No personalization, slow rebuild

Real failure: Store had 50K products with catalog price rules that changed daily. Price calculation took 3 seconds per category page. Fix: Pre-calculate all prices in index table, rebuild via cron every 15 minutes. Category page load: 200ms. Trade-off: Price rule changes take 15 minutes to appear. Business accepted this.

Price Rule Performance

Catalog Price Rules:
├── Evaluated on: Product save, rule save, cron
├── Stored in: catalogrule_rule + catalogrule_product
├── Performance impact: Rule count × product count
└── 100 rules × 50K products = 5M rows in rule_product

Cart Price Rules:
├── Evaluated on: Cart update, coupon apply
├── Stored in: salesrule_rule + salesrule_coupon
├── Performance impact: Rule count per cart
└── 100 rules × 10 items = 1000 evaluations

Optimization:
├── Limit catalog rules to < 50
├── Use simple conditions (attribute = value)
├── Avoid complex combinations (AND/OR)
├── Cache rule results aggressively
└── Monitor rule evaluation time in profiler

Multi-Currency Pricing

``
Storage Options:

1. Same table (default):
   ├── catalog_product_entity_decimal with store_id
   ├── Simple: One table for all currencies
   ├── Performance: Extra rows per currency
   └── 50K products × 3 currencies = 150K rows

2. Separate price index:
   ├── Dedicated price index per currency
   ├── Fast: Direct lookup, no JOIN
   ├── Trade-off: Sync complexity
   └── Recommended for > 3 currencies

Real issue: Store with 10 currencies had 500K price rows. Price query took 500ms due to index size. Fix: Separate price index per currency, query only active currency. Price query: 20ms.
```

Catalog Size Benchmarks

Small Store (< 1K products):
├── EAV: Fine (default Magento)
├── Database: Single server sufficient
├── Search: MySQL full-text acceptable
├── Reindex: < 5 minutes
└── Infrastructure: $200/month total

Medium Store (1K-50K products):
├── EAV: Enable flat catalog
├── Database: Consider read replicas
├── Search: Elasticsearch recommended
├── Reindex: 15-60 minutes
└── Infrastructure: $1000/month total

Large Store (50K-500K products):
├── EAV: Required (flat catalog rebuild too slow)
├── Database: Master-slave, connection pooling
├── Search: Elasticsearch cluster (3+ nodes)
├── Reindex: 2-8 hours (schedule overnight)
└── Infrastructure: $5000/month total

Enterprise (500K+ products):
├── EAV: With custom attribute optimization
├── Database: Sharding or specialized DB
├── Search: Elasticsearch with dedicated masters
├── Reindex: 12-24 hours (partial reindex critical)
└── Infrastructure: $20000+/month total

Real Scale Decisions

Scenario: 100K products, 200 attributes, 3 currencies

Decision 1: Flat catalog?
├── Pro: 10x faster product listing
├── Con: 2 hour rebuild time
├── Decision: YES (schedule rebuild at 3 AM)
└── Impact: Category pages load in 300ms vs 3s

Decision 2: Elasticsearch cluster?
├── Pro: Faceted search, relevance, speed
├── Con: $600/month extra, operational complexity
├── Decision: YES (search is critical for conversion)
└── Impact: Search results in 50ms vs 500ms

Decision 3: Price index?
├── Pro: Pre-calculated prices, fast display
├── Con: 15 minute delay for price changes
├── Decision: YES (business accepts delay)
└── Impact: Price display in 10ms vs 200ms

Total infrastructure: $3000/month
Performance improvement: 10x faster pages
Business impact: 15% conversion improvement
Revenue impact: $150K/year additional revenue
ROI: 50x

Reindex Strategy

Full Reindex:
├── When: After major data changes, attribute additions
├── Duration: 2-8 hours (depends on catalog size)
├── Impact: Store works but search may be stale
└── Schedule: Off-peak hours (2-5 AM)

Partial Reindex:
├── When: Product save, category change, price update
├── Duration: 5-30 seconds per product
├── Impact: Minimal (background process)
└── Magento default: Triggered on entity save

Scheduled Reindex:
├── When: Periodic bulk updates, cron-based
├── Duration: Depends on changes since last run
├── Impact: Background, configurable frequency
└── Recommended: Every 15 minutes for active stores

Real issue: Store ran full reindex every hour. During reindex, search showed stale results for 30 minutes. Fix: Switch to partial reindex on save + scheduled reindex every 15 minutes. Search freshness improved from hourly to near-real-time.

Product Import at Scale

``
Import Methods:

1. Admin CSV Import:
   ├── Limit: 10K products per import
   ├── Duration: 30-60 minutes
   ├── Memory: 512MB-2GB
   └── Use case: Small stores, one-time imports

2. API Import:
   ├── Limit: 100 products per request
   ├── Duration: 1-5 seconds per batch
   ├── Memory: Minimal per request
   └── Use case: Real-time sync, small updates

3. Direct Database Import:
   ├── Limit: 100K+ products
   ├── Duration: 5-30 minutes
   ├── Memory: 1-4GB
   └── Use case: Large imports, data migration

4. Queue-Based Import:
   ├── Limit: Unlimited (queue processes async)
   ├── Duration: 1-24 hours (depends on queue depth)
   ├── Memory: Minimal per worker
   └── Use case: Large stores, continuous sync

Best Practice: For > 10K products, use queue-based import. Process 100 products per queue message. Monitor queue depth. Alert if > 1 hour behind.