🗂️ 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/smart-phones

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

---

🎯 Business Superpower — Multi-Level Product Visibility

A product usually belongs to the deepest (leaf) category.

Example:

Electronics → Mobiles → Smartphones  
                        ↑  
                 Samsung Galaxy

But customers may browse or search from any level.

They might open:

• Electronics
• Mobiles
• Smartphones

👉 In all cases, the product should appear.

---

🧠 How This Design Enables It

Because each category stores its path, we instantly know all its ancestors.

Smartphones → path = "1/2/3"

So the product automatically belongs to:

• 1 → Electronics
• 1/2 → Mobiles
• 1/2/3 → Smartphones

No extra mapping tables.

No complex recursion.

---

⚡ Example Queries

Show products under Mobiles (including all children)

SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.path LIKE '1/2/%';

This returns items from:

• Smartphones
• Feature Phones
• any future subcategory

Show products under Electronics

WHERE c.path LIKE '1/%';

Boom 💥
Entire department.

💰 Business Advantages

✅ Better product discovery

Users don’t need perfect navigation.

✅ Faster search & filtering

No recursive joins required.

✅ Powerful marketing pages

Create pages like:

• “Best Mobiles”
• “Top Electronics Deals”

✅ Smarter recommendations & ads

Target an entire branch of the catalog.

✅ Future proof

Add deeper levels anytime → queries still work.

---

🏪 Real Outcome

If a Samsung Galaxy s23 FE is stored in:

Electronics → Mobiles → Smartphones

It will automatically appear in:

• Electronics listing
• Mobiles listing
• Smartphones listing
• search results
• recommendations
• breadcrumbs

✨ without extra logic.

🏆 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 🚀