Design a product catalog service that supports hierarchical categories implemented with a self-referencing table. Requirements: ( 1) Define the core read/write APIs (e.g., createCategory, moveCategory, listChildren, getBreadcrumbs, createProduct, updateAttributes, attachProductToCategory, search/filter/paginate products within a category). ( 2) Propose the relational schema, including a Categories table with a self-join (id, parent_id, name, path/slug, sort_order), a Products table (id, sku, name, attributes JSON, status), and a ProductCategory join table; discuss indexes required for fast tree traversal and product listing. ( 3) Explain strategies to query the full subtree efficiently (e.g., adjacency list with recursive CTEs vs. materialized paths vs. closure tables) and the trade-offs among them. ( 4) Address scaling: caching hot category pages, pagination, denormalized search indexes, consistency choices, write patterns (bulk imports), and multi-region read replicas. ( 5) Cover data integrity (cycles prevention, referential constraints), API versioning, and rollout plans for schema changes.

This question evaluates system design and data modeling skills for hierarchical data, covering relational schema design, API surface definition, query performance, caching, and data integrity concerns in a product catalog context.

How do I approach System Design interview questions?

System Design questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master system design interviews.

What difficulty level is this interview question?

This is a hard difficulty System Design question, commonly asked during Onsite rounds at Instacart.

What role is this question designed for?

This question is commonly asked for Software Engineer candidates at Instacart during technical interviews.

Design a hierarchical catalog service | Instacart Interview Question

Design a Product Catalog Service with Hierarchical Categories

Context

Design a read-heavy product catalog for an e-commerce application. Categories are hierarchical (tree) and implemented using a self-referencing relational table. Products can belong to one or more categories. The service must expose read/write APIs, support efficient subtree queries, and scale for high-traffic browsing.

Assumptions (you may refine during discussion):

Primary database is relational (e.g., PostgreSQL/MySQL).
Category updates are infrequent compared to reads; product updates are moderate; product browsing is heavy.
Soft-deletes preferred; strong consistency for writes within the database; eventually consistent caches/search.

Requirements

Define core APIs (read/write):
- createCategory, updateCategory, moveCategory, deleteCategory
- listChildren, getBreadcrumbs, getSubtreeSummary
- createProduct, updateAttributes
- attachProductToCategory, detachProductFromCategory
- search/filter/paginate products within a category (optionally include descendants)
Propose a relational schema:
- Categories table with self-join: id, parent_id, name, slug, path/materialized identifier, sort_order
- Products table: id, sku, name, attributes JSON, status
- ProductCategory join table
- Describe indexes for fast tree traversal and product listing
Explain subtree query strategies and trade-offs:
- Adjacency list with recursive CTEs
- Materialized paths
- Closure tables (ancestor/descendant pairs)
Address scaling:
- Caching hot category pages and product lists
- Pagination approach
- Denormalized search indexes
- Consistency choices
- Write patterns (bulk imports)
- Multi-region read replicas
Cover data integrity and change management:
- Prevent cycles; enforce referential integrity
- API versioning
- Rollout plans for schema changes

Context

Assumptions (you may refine during discussion):

Primary database is relational (e.g., PostgreSQL/MySQL).

Category updates are infrequent compared to reads; product updates are moderate; product browsing is heavy.

Soft-deletes preferred; strong consistency for writes within the database; eventually consistent caches/search.

Requirements

Define core APIs (read/write):

createCategory, updateCategory, moveCategory, deleteCategory
listChildren, getBreadcrumbs, getSubtreeSummary
createProduct, updateAttributes
attachProductToCategory, detachProductFromCategory
search/filter/paginate products within a category (optionally include descendants)

Propose a relational schema:

Categories table with self-join: id, parent_id, name, slug, path/materialized identifier, sort_order
Products table: id, sku, name, attributes JSON, status
ProductCategory join table
Describe indexes for fast tree traversal and product listing

Explain subtree query strategies and trade-offs:

Adjacency list with recursive CTEs
Materialized paths
Closure tables (ancestor/descendant pairs)

Address scaling:

Caching hot category pages and product lists
Pagination approach
Denormalized search indexes
Consistency choices
Write patterns (bulk imports)
Multi-region read replicas

Cover data integrity and change management:

Prevent cycles; enforce referential integrity
API versioning
Rollout plans for schema changes

Design a hierarchical catalog service

Quick Overview

Design a Product Catalog Service with Hierarchical Categories

Context

Requirements

Solution

Comments (0)

Design a hierarchical catalog service

Quick Overview

Design a Product Catalog Service with Hierarchical Categories

Context

Requirements

Solution

Comments (0)