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

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 🚀