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

Source: Dev.to

When building an e‑commerce application, categories look simple at first — until your product count grows and the 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 over‑engineering.

The Common Mistake

Many apps start with separate tables:

categories
sub_categories
sub_sub_categories

This breaks immediately when:

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

The Scalable Solution (Single Categories Table)

Use one table with a self‑reference.

CREATE TABLE categories (
    id          UUID PRIMARY KEY,          -- or BIGINT
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) UNIQUE NOT NULL,
    parent_id   UUID REFERENCES categories(id),
    level       INT NOT NULL,
    path        VARCHAR(500) NOT NULL,
    sort_order  INT DEFAULT 0,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This supports unlimited nesting and clean queries.

How Hierarchy Works

Example structure

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

Stored data

Field Breakdown (The Important Part)

1️⃣ slug – URL‑friendly identifier

A slug is a readable string used in URLs.

"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 the 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.

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 the UI.

• Without it → unpredictable order
• With it → business‑controlled order

Query example

SELECT * FROM categories ORDER BY sort_order ASC;

Used for

• Navbar ordering
• Featured categories
• Seasonal rearrangements

Why Use level + path Together?

They solve different problems, not duplication.

Product Association

Products usually belong to the leaf category.

CREATE TABLE products (
    id          UUID PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) UNIQUE NOT NULL,
    price       NUMERIC(10,2) NOT NULL,
    category_id UUID REFERENCES 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.