Designing a Scalable Udemy-Style Taxonomy Hierarchy in SQL

Online learning platforms like Udemy, Coursera, or e-commerce platforms like Amazon organize millions of items into structured taxonomies. On Udemy, a user navigates through:

1. Category (Level 1): Broad domain (e.g., Software Development, Business)
2. Sub-category (Level 2): Intermediate grouping (e.g., Programming Languages, Databases)
3. Topic (Level 3): Specific subject (e.g., Python, JavaScript, PostgreSQL)

Designing a storage and querying system for this taxonomy requires balancing schema flexibility, read latency, and storage overhead.

---

1. Domain Requirements and Constraints

Before modeling the data, several domain characteristics and business rules must be established:

• Strict 3-Level Depth: In Udemy’s case, the hierarchy depth is fixed to 3 tiers: Category $\rightarrow$ Sub-category $\rightarrow$ Topic.
• Single Parent Invariant: Every topic belongs to exactly one sub-category, and every sub-category belongs to exactly one category. Categories have no parents (parent_id IS NULL).
• High Read-to-Write Ratio: Changes to the taxonomy are rare (mostly administrative updates), whereas reads happen on almost every page view (navigation menus, search facets, breadcrumbs, and category landing pages).
• Scale: While 3 levels might seem modest, child nodes (topics) can grow significantly (tens or hundreds of thousands), and associated relations can scale into millions of rows.
• Order/Rank: Nodes often need to be sorted by relevance, popularity, or editorial rank (e.g., displaying the top $K$ sub-categories for each category).

---

2. Choosing the Right Architecture: Relational vs. Graph Database

Because taxonomies represent a directed acyclic graph (specifically an arborescence or forest of trees), graph databases like Neo4j or Amazon Neptune are often considered. However, an RDBMS (like MySQL or PostgreSQL) is generally superior for this specific use case:

Consideration	Graph Database (e.g., Neo4j)	Relational Database (SQL)
Use Case Fit	Deep, arbitrary depth traversals, variable hops, shortest path queries.	Fixed-depth hierarchies (max depth = 3).
Query Complexity	Cypher is natural for graphs, but operational overhead is high.	Simple self-joins easily resolve fixed depth.
Operational Overhead	Managing a dedicated graph cluster adds complexity and cost.	Standard RDBMS with primary/foreign keys and indexes.
Transactional Integrity	Often eventual consistency or specialized tuning.	ACID compliance, robust foreign key constraints, easy caching.

Because the depth is strictly bounded ($N = 3$), a relational database provides sub-millisecond lookups using standard B-tree indexes without the maintenance burden of a dedicated graph store.

---

3. Schema Design: The Unified Self-Referencing Table

Rather than creating separate tables (categories, sub_categories, topics), a single unified table named topics (or taxonomy_nodes) provides superior flexibility and avoids redundant schema migrations.

Why a Single Table?

If you maintain 3 separate tables, introducing a 4th level or promoting a topic to a sub-category requires complex migrations across tables. In a single table, it is just an update to a type and a parent_id pointer.

erDiagram
    TOPICS {
        bigint id PK
        string name
        int type "1=Category, 2=Sub-Category, 3=Topic"
        bigint parent_id FK
        float score "Popularity or ordering score"
        timestamp created_at
        timestamp updated_at
    }
    TOPICS ||--o{ TOPICS : "parent_id references id"

Table Definition (PostgreSQL / MySQL)

CREATE TABLE topics (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type TINYINT NOT NULL, -- 1: Category, 2: Sub-category, 3: Topic
    parent_id BIGINT NULL,
    score FLOAT NOT NULL DEFAULT 0.0, -- Used for sorting popular/trending items
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_topics_parent FOREIGN KEY (parent_id) REFERENCES topics(id) ON DELETE CASCADE
);

• type: Indicates hierarchy level (1 for root categories, 2 for sub-categories, 3 for topics).
• parent_id: Points to another record in the same table. If type = 1, parent_id is NULL.
• score: A pre-aggregated metric (e.g., total enrolled students, total courses, or manual ranking) used to sort popular categories or topics efficiently without doing dynamic counts on massive course tables.

---

4. Indexing Strategy

Without targeted compound indexes, querying hierarchical children or sorting by score triggers expensive full-table scans and file-sorts.

Required Indexes

1. Primary Key: B-Tree index on id (default).
2. Parent Filtering and Ordering: When fetching all sub-categories of a category ordered by score:

   CREATE INDEX idx_topics_parent_score ON topics (parent_id, score DESC);

3. Type-Based Filtering: When retrieving all top-level categories:

   CREATE INDEX idx_topics_type_score ON topics (type, score DESC);

---

5. Core Queries

Query 1: Breadcrumb Path Generation (Topic $\rightarrow$ Root)

When a user lands on a course topic page (e.g., PostgreSQL, id = 301), the UI needs to display: Software Development > Databases > PostgreSQL

Because the depth is fixed at 3, we can achieve this with bounded self-joins on the topics table without recursive CTEs:

SELECT 
    l1.id AS category_id,
    l1.name AS category_name,
    l2.id AS subcategory_id,
    l2.name AS subcategory_name,
    l3.id AS topic_id,
    l3.name AS topic_name
FROM topics l3
LEFT JOIN topics l2 ON l3.parent_id = l2.id
LEFT JOIN topics l1 ON l2.parent_id = l1.id
WHERE l3.id = :target_topic_id;

• By using LEFT JOIN, if the query is passed a node that is a sub-category (type = 2), l1 fetches its category, while l3 represents the node itself, allowing the application to parse breadcrumbs dynamically.
• With index lookups on primary keys (parent_id = id), this query runs in under a millisecond even on multi-million-row tables.

---

Query 2: Fetching All Direct Children of a Category

When viewing a category page, we often need to list all child sub-categories sorted by popularity:

SELECT id, name, score
FROM topics
WHERE parent_id = :parent_id
ORDER BY score DESC;

Utilizes idx_topics_parent_score for an index-only or index-assisted scan with zero in-memory sorting.

---

Query 3: Top $K$ Sub-categories per Category (Window Functions)

In platform navigation menus (like Udemy’s mega dropdown), you typically display every main category, and under each category, only the top 5 most popular sub-categories.

Doing this via multiple queries (N+1 problem) or fetching every sub-category into application memory is inefficient. Instead, we use SQL Window Functions (ROW_NUMBER() or DENSE_RANK()):

WITH ranked_subcategories AS (
    SELECT 
        c.id AS category_id,
        c.name AS category_name,
        s.id AS subcategory_id,
        s.name AS subcategory_name,
        s.score AS subcategory_score,
        ROW_NUMBER() OVER (
            PARTITION BY c.id 
            ORDER BY s.score DESC
        ) AS rank_num
    FROM topics c
    JOIN topics s ON s.parent_id = c.id
    WHERE c.type = 1 -- Only root categories
      AND s.type = 2 -- Only sub-categories
)
SELECT 
    category_id,
    category_name,
    subcategory_id,
    subcategory_name,
    subcategory_score
FROM ranked_subcategories
WHERE rank_num <= 5
ORDER BY category_id, rank_num;

How It Works:

1. PARTITION BY c.id: Groups the results logically by each primary category.
2. ORDER BY s.score DESC: Assigns sequential ranks (1, 2, 3...) to the sub-categories within each partition.
3. WHERE rank_num <= 5: Filters down only the top 5 per partition in a single, set-based database pass.

---

6. Performance and Scalability Considerations

1. Fixed vs. Arbitrary Depth:

   • Fixed-depth structures ($N \le 3$) should rely on explicit self-joins. They produce predictable query plans that query planners optimize easily.
   • If the depth becomes dynamic or arbitrary, standard relational self-joins break down, requiring Recursive Common Table Expressions (WITH RECURSIVE), Nested Sets, Path Enumeration (Materialized Path), or Adjacency Lists.

2. Caching Strategy:

   • Taxonomy data rarely changes. The output of the top-categories-and-subcategories query should be cached at the application layer (e.g., Redis) or CDN edge with long TTLs (hours or days).
   • Cache invalidation can be hook-driven: whenever an administrator modifies a row in topics, trigger an event that invalidates the cached taxonomy payload.

3. Compound Key Index Sizing:

   • Ensure (parent_id, score DESC) is ordered appropriately so the engine can satisfy range/equality conditions on parent_id while walking the index in pre-sorted order for score.

---

Summary

Designing a Udemy-like taxonomy hierarchy does not require graph engines or multi-table fragmentation. A single, self-referencing SQL table combined with:

• An explicit type column
• Self-joins for breadcrumb paths
• ROW_NUMBER() window functions for top-$K$ navigation
• Strategic composite B-tree indexes

provides a performant, maintainable, and highly scalable foundation for e-learning and e-commerce platforms.