Lorixen.
EN

Nested product categories in your database, done simply

Short answer: For most shops and business apps, store each category with a parent_id pointing to its parent category. It's the simplest design to understand and edit. To get a whole branch, such as every subcategory under "Garden", or the path back to the top for breadcrumbs, use a recursive query (WITH RECURSIVE), which PostgreSQL, SQLite and modern MySQL all support. Add guards against loops and an index on parent_id, and you can handle deep trees without special tables or extensions.

The design: one table, one parent column

CREATE TABLE categories (
  id serial PRIMARY KEY,
  name text NOT NULL,
  parent_id integer REFERENCES categories(id)
);
CREATE INDEX ON categories (parent_id);

Top-level categories have no parent. "Garden" might have "Tools", which has "Hand tools", which has "Pruners". Moving a category to another branch means changing one parent_id. Renaming means changing one name.

This is often called an adjacency list. It suits catalogues that change through an admin screen, because every change touches a single row.

Fetching a whole branch

The common job: show all products in "Garden" and every category beneath it. PostgreSQL's documentation explains that with RECURSIVE, a WITH query can refer to its own output. The shape is always a starting query, then UNION or UNION ALL, then a recursive part that refers back to the results so far.

WITH RECURSIVE branch AS (
  SELECT id FROM categories WHERE id = 12      -- Garden
  UNION ALL
  SELECT c.id FROM categories c
  JOIN branch b ON c.parent_id = b.id
)
SELECT p.* FROM products p
WHERE p.category_id IN (SELECT id FROM branch);

The first part selects Garden. The recursive part keeps adding children of whatever has been found, until there are none left.

For a product in "Pruners", the breadcrumb is Garden › Tools › Hand tools › Pruners. Walk upwards instead of downwards:

WITH RECURSIVE path AS (
  SELECT id, name, parent_id, 1 AS depth FROM categories WHERE id = 57
  UNION ALL
  SELECT c.id, c.name, c.parent_id, p.depth + 1
  FROM categories c JOIN path p ON c.id = p.parent_id
)
SELECT name FROM path ORDER BY depth DESC;

Guard against loops

If someone makes "Tools" a child of "Hand tools" while "Hand tools" is already inside "Tools", the tree has a loop, and a recursive query could run forever. PostgreSQL's documentation stresses making sure the recursive part eventually returns no rows, and describes a CYCLE clause that detects cycles and stops following them. In other databases, track the path or depth and stop at a sensible limit.

Better still, prevent loops when saving: before changing a category's parent, check that the new parent isn't the category itself or one of its descendants. Your admin screen should refuse that move with a clear message.

Ordering the tree for menus

Navigation menus and admin screens need the tree in display order. PostgreSQL offers SEARCH DEPTH FIRST and SEARCH BREADTH FIRST clauses that add a sort column to recursive results. SQLite's documentation shows how to control depth-first or breadth-first order with ORDER BY in a recursive query. Add a position column if staff need to choose the order of siblings.

Showing product counts per category

Menus often show "Hand tools (24)". Counting products for every category in every branch on each page load gets expensive as the catalogue grows. Two simple approaches work well: compute the counts with one recursive query when the catalogue changes and store them in a small table, or calculate them on a schedule. Either way, shoppers see accurate counts without the database repeating the same work for every visitor.

Importing categories from a supplier or marketplace

Supplier feeds often describe categories as a path, such as "Garden > Tools > Hand tools". To import, split the path, then look up or create each level in turn, using the previous level's ID as the parent. Store the supplier's own category code alongside your ID, so the next import matches existing categories instead of creating duplicates. Review new categories before they appear on the site, because supplier naming rarely matches how your customers browse.

When to consider other designs

The parent column covers most small and medium catalogues. Other patterns exist for special cases:

  • Closure table: a separate table storing every ancestor–descendant pair. Very fast reads for "everything under X", more work on every move.
  • Materialised path: storing /12/34/57/ on each row. Easy "starts with" queries, but every descendant's path changes on a move.
  • Nested sets: fast reads, slow and fiddly writes. Rarely worth it for catalogues that staff edit.

Start with the parent column and recursive queries. Only switch if you measure a real problem.

Practical tips

  • Index parent_id. Every step of the recursion looks it up.
  • Cache the menu. The category tree changes rarely; build it once and refresh it when categories change.
  • Decide what deleting a category does. Block deletion while it has children or products, or move them to the parent.
  • Keep URLs stable. If category URLs include the full path, moving a category changes its URL; plan redirects.
  • Products in several categories need a join table rather than a single category_id.

A worked example

An illustrative Portland hardware store's online catalogue started with two levels of categories stored as separate tables. When the owner wanted a third level for tool types, every query and admin screen had to change. The developer moved to a single categories table with parent_id, used recursive queries for "all products in this branch" and breadcrumbs, and added a check that stopped staff moving a category under its own child. Adding a fourth level later needed no code changes at all.

FAQ

Is a recursive query slow?

For typical catalogue sizes with an index on parent_id, it's usually fast. Cache results that are read constantly, like menus.

Does MySQL support this?

Recent MySQL versions support recursive common table expressions. Older versions need workarounds or one of the other designs.

How deep can the tree go?

As deep as you like technically. For shoppers, three or four levels is usually plenty.

Should tags and categories share the table?

Usually not. Tags are flat and many-to-many; categories are a hierarchy.

If your catalogue or app needs a category structure that grows with you, tell us how your products are organised and we'll suggest a design.

Sources

WhatsAppBook a call