🗂️ Designing a Scalable Category System for an E-Commerce App

Posted by DevegygiebyOL

When building an e-commerce application, categories look simple at first — until your product count grows and business asks for:

  • sub-categories
  • nested menus
  • breadcrumbs
  • SEO-friendly URLs
  • easy reordering

This README explains a scalable, production-ready category design used in real-world systems, without overengineering.

❌ The Common Mistake

Many apps start with this: 

categories
sub_categories
sub_sub_categories

This breaks immediately when:

  • you need more depth
  • hierarchy changes
  • queries become complex

✅ The Scalable Solution (Single Categories Table)

Use one table with a self-reference. 

categories
-----------
id          UUID / BIGINT (PK)
name        VARCHAR(255)
slug        VARCHAR(255) UNIQUE
parent_id   UUID / BIGINT (FK → categories.id, NULL)
level       INT
path        VARCHAR(500)
sort_order  INT
is_active   BOOLEAN
created_at  TIMESTAMP
updated_at  TIMESTAMP

This supports unlimited nesting and clean queries.

🌳 How Hierarchy Works

Example structure 

Electronics
 └── Mobiles
      ├── Smartphones
      └── Feature Phones

Stored data

id name parent_id level path sort_order
1 Electronics NULL 0 1 1
2 Mobiles 1 1 1/2 1
3 Smartphones 2 2 1/2/3 1
4 Feature Phones 2 2 1/2/4 2

🔑 Field Breakdown (The Important Part)

1️⃣ slug – URL-friendly identifier

A slug is a readable string used in URLs.

Example: 

"Smart Phones" → "smart-phones"

Used for: 

/category/electronics/mobiles/smartphones

Why slugs matter:

  • SEO friendly
  • Stable URLs
  • No exposed IDs

2️⃣ level – Depth of the category

level tells how deep a category is. 

level 0 = root category
level 1 = sub-category
level 2 = sub-sub-category

Why it exists:

  • Show only top-level categories on homepage
  • Restrict max depth
  • Simple filtering

Query example: 

SELECT * FROM categories WHERE level = 0;

3️⃣ path – Full hierarchy (Materialized Path)

path stores the entire lineage from root → current node.

Example: 

Electronics → Mobiles → Smartphones
path = "1/2/3"

Why it’s powerful:

  • Fetch entire subtrees without recursion
  • Build breadcrumbs easily
  • Generate SEO URLs

Query example: 

SELECT * FROM categories WHERE path LIKE '1/2/%';

4️⃣ sort_order – Display control (NOT hierarchy)

sort_order controls how categories appear in UI.

Without it → unpredictable order

With it → business-controlled order

Query example: 

ORDER BY sort_order ASC;

Used for:

  • Navbar ordering
  • Featured categories
  • Seasonal rearrangements

💡 Why Use level + path Together?

Use case level path
Top-level filtering ✅ ❌
Max depth validation ✅ ❌
Subtree queries ❌ ✅
Breadcrumbs ❌ ✅
SEO URLs ❌ ✅

They solve different problems, not duplication.

🌟 Product Association

Products usually belong to the leaf category. 

products
---------
id
name
slug
price
category_id → categories.id

🏆 Final Recommendation

  • ✅ Single categories table
  • ✅ parent_id for structure
  • ✅ level for depth logic
  • ✅ path for fast reads
  • ✅ slug for clean URLs
  • ✅ sort_order for UI control

This design scales from startup MVP → large marketplace without schema changes.

💬 Interview One-Liner

A scalable category system uses a self-referencing table with materialized paths to support unlimited depth, fast reads, clean URLs, and UI-controlled ordering.

If you liked this design, feel free to ⭐ the repo or reuse it in your project.

Happy building 🚀