Product Catalog Development for E-Commerce

Product Catalog Development for E-Commerce

The catalog is the central module of an e-commerce store. Its architecture determines everything: search speed, navigation convenience, SEO traffic, simplicity of managing assortment. Errors in catalog data model are the most expensive because they're fixed with data migration and rework of dependent modules.

Category Hierarchy

Category tree is stored in database. Two common approaches:

Adjacency List — each record stores parent_id. Simple to write, but selecting entire tree requires recursive CTE:

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

Nested Sets (MPTT) — each record stores lft and rgt values. Subtree selection: WHERE lft BETWEEN :parent_lft AND :parent_rgt — one query without recursion. Writing is complex: when adding node, update all right neighbors. Package kalnoy/nestedset for Laravel.

Closure Table — separate table of all ancestor-descendant pairs. Most flexible, uses more space. For complex tree operations (moving subtrees) optimal.

Recommendation: for catalogs up to 10,000 categories Adjacency List with cached tree in Redis — sufficient. MPPT — with frequent subtree queries without cache.

Attributes Model

Products of different categories have different attribute sets. Three approaches:

Table with fixed columns: products.color, products.size, products.weight. Works only with homogeneous assortment. Adding new attribute — ALTER TABLE, migration, deploy.

EAV (Entity-Attribute-Value):

attributes (id, name, type, unit, filterable, sortable, category_id)
product_attributes (product_id, attribute_id, value_text, value_numeric, value_boolean)

Flexible, but slow with JOINs. For attribute filtering need Elasticsearch or denormalized index.

JSONB column in PostgreSQL:

ALTER TABLE products ADD COLUMN attributes JSONB;
-- Index for specific attribute:
CREATE INDEX ON products ((attributes->>'color'));
-- GIN-index for any attribute:
CREATE INDEX ON products USING GIN (attributes);

Compromise: EAV flexibility without excessive JOINs. Suitable for catalogs up to 500,000 products.

Product Variants (SKU)

Product with variants (color × size) — common task. Two patterns:

Simple SKU: each combination — separate record in products. Simple, but complex to manage "parent" card.

Parent-Child:

products (id, type, parent_id, sku, name, price, stock)
-- type: 'simple' | 'variable' | 'variant'
-- variant: parent_id → variable product

When displaying card: load parent + all variants. User selects attribute combination → find matching variant → update price, photo, availability.

Variant matrix for displaying selection:

type VariantMatrix = {
  [attributeId: string]: {
    [value: string]: {
      variantId: number;
      inStock: boolean;
      price: number;
    }
  }
}

URL Structure and SEO

Category URLs — critical for SEO. Three options:

• Flat: /catalog/laptops — simple, loses hierarchy context
• Hierarchical: /catalog/electronics/computers/laptops — better for SEO, complex on category move
• Hybrid: /laptops-c142 — readable slug + unique ID (resistant to renames)

For filtered pages: /laptops?brand=apple&ram=16 with canonical to /laptops or separate SEO pages for popular combinations (/laptops-apple-16gb as static aggregator page).

Schema.org: ItemList on category pages with ListItem for each product in listing.

Pagination and Infinite Scroll

Offset pagination: LIMIT 48 OFFSET 144. Works, but deep pages (OFFSET 10000) PostgreSQL still reads 10048 rows. Solution — keyset pagination:

-- Instead of OFFSET use cursor on last ID
SELECT * FROM products
WHERE (sort_value, id) > (:last_sort_value, :last_id)
ORDER BY sort_value, id
LIMIT 48;

Keyset pagination instant at any depth, but doesn't support jump to arbitrary page.

Infinite scroll vs pagination: for mobile — infinite scroll with IntersectionObserver, for desktop with SEO priority — classic pagination (search engines index numbered pages better).

Catalog Management in CMS

Administrative catalog interface:

• Bulk editing: select 50 products → change category/status/price
• CSV/XLSX import: column mapping, preview with errors, background load via queue
• Drag-and-drop category sorting: visual tree with drag capability
• Attribute management: add attribute to category → appears on edit forms of all category products

For bulk import: Laravel Jobs + Horizon. File uploaded to S3, task pulled from queue, parsed line-by-line (via league/csv or PhpSpreadsheet), products inserted batch by 100.

Caching

Catalog pages — main DB load. Caching strategy:

On product change invalidate only pages where it appears. Cache tags in Laravel: Cache::tags(['category:electronics'])->flush().

Timeline

• Basic catalog (categories, product list, card, pagination): 2–3 weeks
• With variants, EAV attributes, import and caching: 4–7 weeks
• Elasticsearch integration for search and filtering adds 2–3 weeks