KAHIBARO
Discord Login Register ☰
☰

31.4. Categories

Table of Contents

Understanding Categories in an E‑Commerce Backend

In an e‑commerce backend, categories give structure to your product catalog. They help users find products and help your system organize, filter, and cache product data efficiently.

This chapter focuses on how to model, store, and work with categories for the e‑commerce project. We will assume you already know the basics of REST APIs, databases, and routing from earlier chapters, and the general e‑commerce architecture from the project overview.

What Categories Are and Why They Matter

Categories are labels that group similar products together. Some examples:

They provide:

In a backend, categories are not just labels, they are entities with their own data and relationships.

Typical fields for a category:

FieldTypeExamplePurpose
idinteger / UUID42Primary key
namestring"Laptops"Human readable name
slugstring"laptops"URL friendly identifier
descriptiontext / nullable"Portable computers..."Optional longer description
parent_idinteger / nullnull or 10For hierarchical categories
is_activebooleantrueSoft enable / disable
created_attimestampAudit / sorting
updated_attimestampAudit / caching invalidation

Key rule: Treat categories as first‑class entities with their own table, API endpoints, and validation, not as free‑text tags on products.

Flat vs Hierarchical Categories

There are two common ways to model categories in an e‑commerce system.

Flat Category Structure

In a flat structure, every category is independent.

Example table:

idnameslug
1"Shoes""shoes"
2"T-Shirts""t-shirts"
3"Jeans""jeans"

Typical usage: A very small shop with only a few dozen products.

Hierarchical Category Structure

Most real e‑commerce systems use hierarchical categories. For example:

This hierarchy allows:

Data model example (adjacency list):

idnameslugparent_id
1Electronicselectronicsnull
2Computerscomputers1
3Laptopslaptops2
4Desktopsdesktops2
5Phonesphones1

Here, each row references its parent category.

Important: In a hierarchical model, always validate that a category cannot be its own parent, and cannot create cycles (A is parent of B, B is parent of C, C parent of A).

Database Design for Categories

We will focus on a simple and practical schema that works well with SQL databases like PostgreSQL and with ORMs like SQLAlchemy.

Basic Table Structure

A minimal categories table using adjacency list:

sql 
CREATE TABLE categories (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    parent_id   INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Key points:

Indexes for Performance

Common queries:

You can optimize these with indexes:

sql 
CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_is_active ON categories(is_active);

Rule: Always create an index on slug if you use it in URLs, and on parent_id if you frequently query children categories.

Category Hierarchies and Trees

The adjacency list model uses parent_id to link a category to its parent. This is simple to implement and works well for many backends.

Getting Children and Parents

Typical queries:

sql 
SELECT *
FROM categories
WHERE parent_id = $1
ORDER BY name;
sql 
SELECT *
FROM categories
WHERE parent_id IS NULL
ORDER BY name;
sql 
SELECT parent.*
FROM categories AS child
JOIN categories AS parent ON parent.id = child.parent_id
WHERE child.id = $1;

For deeper trees (grandchildren and beyond), you can use recursive queries or handle recursion in application code.

Recursive Query Example in PostgreSQL

To get all descendants of a category (category itself and children at all levels):

sql 
WITH RECURSIVE category_tree AS (
    SELECT *
    FROM categories
    WHERE id = $1
    UNION ALL
    SELECT c.*
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT *
FROM category_tree;

This is useful when you want to show all products that belong to a category or any of its subcategories.

Representing the Tree in API Responses

A common representation:

json 
{
  "id": 1,
  "name": "Electronics",
  "slug": "electronics",
  "children": [
    {
      "id": 2,
      "name": "Computers",
      "slug": "computers",
      "children": [
        {
          "id": 3,
          "name": "Laptops",
          "slug": "laptops",
          "children": []
        }
      ]
    },
    {
      "id": 5,
      "name": "Phones",
      "slug": "phones",
      "children": []
    }
  ]
}

Backends often provide:

Category API Design

Categories belong both to the admin side and to the public API.

Typical Endpoints

For an admin API:

MethodPathDescription
GET/admin/categoriesList categories (with filters)
POST/admin/categoriesCreate a category
GET/admin/categories/{id}Get a single category
PUT/admin/categories/{id}Update a category, full update
PATCH/admin/categories/{id}Partial update, optional
DELETE/admin/categories/{id}Delete or deactivate a category

For the public storefront API:

MethodPathDescription
GET/categoriesList visible root categories or all categories
GET/categories/treeGet full category tree, for navigation menus
GET/categories/{slug}Get category details by slug
GET/categories/{slug}/childrenGet direct children

Request and Response Models

Example Pydantic models (simplified):

python 
from typing import Optional, List
from pydantic import BaseModel
class CategoryBase(BaseModel):
    name: str
    slug: str
    description: Optional[str] = None
    parent_id: Optional[int] = None
    is_active: bool = True
class CategoryCreate(CategoryBase):
    pass
class CategoryUpdate(BaseModel):
    name: Optional[str] = None
    slug: Optional[str] = None
    description: Optional[str] = None
    parent_id: Optional[int] = None
    is_active: Optional[bool] = None
class Category(BaseModel):
    id: int
    name: str
    slug: str
    description: Optional[str] = None
    parent_id: Optional[int] = None
    is_active: bool
    class Config:
        orm_mode = True
class CategoryTree(Category):
    children: List["CategoryTree"] = []

With CategoryTree.update_forward_refs() you can use nested trees.

Example: Create Category Request

http 
POST /admin/categories
Content-Type: application/json
{
  "name": "Laptops",
  "slug": "laptops",
  "description": "Portable computers for work and gaming.",
  "parent_id": 2,
  "is_active": true
}

Example response:

json 
{
  "id": 7,
  "name": "Laptops",
  "slug": "laptops",
  "description": "Portable computers for work and gaming.",
  "parent_id": 2,
  "is_active": true
}

Validation Rules for Categories

Good validation is critical to keep your category tree consistent and avoid broken navigation.

Common Validation Rules

Below is a table of typical validation checks:

RuleExample of violation
name is required and not empty"" or missing name
slug is required and uniqueTwo categories both with "laptops" slug
slug format is URL friendlyContains spaces or uppercase letters
Valid parent_id or nullParent does not exist
No self parentparent_id == id
No cycles in the treeA is child of B, B child of C, C child of A
Cannot delete category with children (if you require manual cleanup)Deleting "Computers" while "Laptops" exists

Critical consistency rule: Never allow a category to reference itself directly or indirectly as an ancestor. Cycles will break tree traversal and category based product listing.

Example Slug Validation

Simple rules for slugs:

You can validate this with a regex, for example:

If slug is not provided, you can generate one from the name:

But even if you auto generate, you must still verify uniqueness.

Handling Parent Changes

When updating a category, parent changes are tricky.

For example, updating category 3 to have parent 7:

  1. Check category 7 exists.
  2. Ensure category 7 is not a descendant of 3, or you create a cycle.

You can detect cycles by:

Pseudo code example in Python:

python 
def is_descendant(db, potential_descendant_id: int, ancestor_id: int) -> bool:
    current_id = potential_descendant_id
    while current_id is not None:
        category = db.get_category_by_id(current_id)
        if category is None:
            return False
        if category.parent_id == ancestor_id:
            return True
        current_id = category.parent_id
    return False

Use this before allowing parent_id updates.

Linking Products to Categories

The core relationship is between products and categories. This belongs conceptually to both the "Product Management" and "Categories" chapters, but here we focus on how categories typically model this relationship.

One Category vs Multiple Categories

Two approaches:

  1. Single category per product

Product has a category_id foreign key.

sql 
   ALTER TABLE products
   ADD COLUMN category_id INTEGER
   REFERENCES categories(id);

Pros:

Cons:

  1. Multiple categories per product (recommended)

Use a join table product_categories for a many‑to‑many relationship.

sql 
   CREATE TABLE product_categories (
       product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
       category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
       PRIMARY KEY (product_id, category_id)
   );

Pros:

Cons:

Example Queries

All categories for a product:

sql 
SELECT c.*
FROM categories c
JOIN product_categories pc ON pc.category_id = c.id
WHERE pc.product_id = $1;

All products in a category:

sql 
SELECT p.*
FROM products p
JOIN product_categories pc ON pc.product_id = p.id
WHERE pc.category_id = $1;

Products in a category and all its subcategories:

  1. Get all descendant category IDs using a recursive CTE.
  2. Filter product_categories by those IDs.
sql 
WITH RECURSIVE category_tree AS (
    SELECT id
    FROM categories
    WHERE id = $1
    UNION ALL
    SELECT c.id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT p.*
FROM products p
JOIN product_categories pc ON pc.product_id = p.id
WHERE pc.category_id IN (SELECT id FROM category_tree);

Admin Workflows for Managing Categories

The admin interface needs to support common operations around categories.

Create Category Workflow

  1. Admin opens "Create Category" form.
  2. Provides name, optional slug (or auto generate), optional description, optional parent.
  3. Backend validates, creates record, returns new category data.

Why important: If category creation is complicated, admins will avoid organizing the catalog properly.

Update Category Workflow

Typical changes:

When disabling (is_active=false):

Delete Category Workflow

Deleting categories can cause data loss or broken links. Strategies:

StrategyDescriptionProsCons
Hard deleteDelete row from DBSimpleCan break products or orphan subcategories
Soft delete flagis_deleted = true and hide categorySafe by defaultNeed to filter is_deleted = false everywhere
Restricted deleteForbid delete if subcategories or products existProtects integrityAdmin must manually reassign products
Reassign on deleteMove children/products to another category on deleteFlexibleMore complex to implement

In many systems, it is safer to use soft delete or to require explicit reassignment before deletion.

Example of restricted delete logic:

  1. Check if category has children:
sql 
   SELECT COUNT(*)
   FROM categories
   WHERE parent_id = $1;
  1. Check if any product is assigned to this category:
sql 
   SELECT COUNT(*)
   FROM product_categories
   WHERE category_id = $1;
  1. If either count is non zero, return an error like:
json 
   {
     "detail": "Cannot delete category with subcategories or assigned products"
   }

Safe operation rule: For real stores, prefer deactivating categories or using soft deletion instead of hard deletion, to avoid losing relationships and breaking URLs.

Performance Considerations and Caching

Category data changes rarely but is read very often. This makes it ideal for caching.

Caching Category Lists and Trees

Examples of what to cache:

With Redis, you can cache:

When a category is created, updated, or deleted, you should invalidate related keys.

Example invalidation strategy in pseudocode:

python 
def invalidate_category_cache(category_id: int, slug: str):
    redis.delete("category:tree")
    redis.delete(f"category:slug:{slug}")
    # You can also delete parent-related caches if you have them.

Because category trees do not change frequently, you can use a relatively long expiration time, for example 1 hour, and also invalidate manually on writes.

Pagination vs Full Tree

For admin lists, use pagination:

For public navigation, full tree:

The admin list is for editing, and often needs sorting, filtering, pagination. The tree is for the storefront, and usually only a few hundred categories, which can be loaded at once and cached.

Category URLs and SEO Considerations

Category URLs are important for frontend routing and SEO. Even though this is mostly a frontend concern, the backend defines the slugs and ensures uniqueness and stability.

Common patterns:

The backend often only needs slug to identify a category, and the frontend may add the full path for readability. For example:

To support this, you might provide:

Breadcrumb path example response:

json 
{
  "breadcrumbs": [
    { "id": 1, "name": "Electronics", "slug": "electronics" },
    { "id": 2, "name": "Computers", "slug": "computers" },
    { "id": 3, "name": "Laptops", "slug": "laptops" }
  ]
}

To build this, you walk from the category up to the root using parent_id, then reverse the list.

Example: Implementing Category Endpoints with FastAPI and SQLAlchemy

Below is a simplified example combining several ideas from this chapter.

SQLAlchemy Model

python 
from datetime import datetime
from sqlalchemy import Column, Integer, String, Text, Boolean, ForeignKey, DateTime
from sqlalchemy.orm import relationship
from app.db.base_class import Base
class Category(Base):
    __tablename__ = "categories"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), nullable=False)
    slug = Column(String(255), nullable=False, unique=True, index=True)
    description = Column(Text, nullable=True)
    parent_id = Column(Integer, ForeignKey("categories.id"), nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)
    parent = relationship("Category", remote_side=[id], backref="children")

FastAPI Routes (Simplified)

python 
from fastapi import APIRouter, Depends, HTTPException, status
from sqlalchemy.orm import Session
from app.api.deps import get_db
from app.schemas.category import Category, CategoryCreate, CategoryUpdate
from app import crud
router = APIRouter()
@router.get("/categories", response_model=list[Category])
def list_categories(db: Session = Depends(get_db)):
    return crud.category.get_multi(db)
@router.post("/categories", response_model=Category, status_code=status.HTTP_201_CREATED)
def create_category(category_in: CategoryCreate, db: Session = Depends(get_db)):
    # validate parent
    if category_in.parent_id is not None:
        parent = crud.category.get(db, id=category_in.parent_id)
        if not parent:
            raise HTTPException(status_code=400, detail="Parent category not found")
    # validate slug uniqueness
    existing = crud.category.get_by_slug(db, slug=category_in.slug)
    if existing:
        raise HTTPException(status_code=400, detail="Slug already in use")
    return crud.category.create(db, obj_in=category_in)
@router.get("/categories/{slug}", response_model=Category)
def get_category(slug: str, db: Session = Depends(get_db)):
    category = crud.category.get_by_slug(db, slug=slug)
    if not category:
        raise HTTPException(status_code=404, detail="Category not found")
    return category

This is only a partial sketch, but it shows how the concepts of validation, slug lookup, and parent checking come together.

Summary

In this chapter you learned how to handle categories specifically for an e‑commerce backend:

These patterns will be used in the rest of the e‑commerce backend project, especially when connecting categories to products, search, caching, and admin features.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3085 / 24h
Impressum / Imprint
Datenschutz / Data Policy