Designing a Scalable Product & Service Database (With Categories, Subcategories & Images)

By Prathamesh Majalkar • Aug 10, 2025

A well-structured database is the backbone of any e-commerce or service listing platform. Without proper design, you risk slow queries, inconsistent data, and poor scalability. In this post, I’ll walk you through an optimized structure for handling products or services with categories, subcategories, and images.

Why Use Categories & Subcategories

• Flexibility: Organize products without repeating category names.
• Normalization: Avoids redundant data and keeps relationships clean.
• Future-proofing: Easy to add more hierarchy levels later.
• Performance: Indexed IDs improve query speed for filtering.

Field Design & Common Columns

Each table includes: id (primary key), status (active/inactive), created_at, and updated_at. Images are stored as file paths or URLs instead of binary data to keep the database lightweight.

SQL Schema

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  image VARCHAR(255),
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE subcategories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  image VARCHAR(255),
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subcategory_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  image VARCHAR(255),
  price DECIMAL(10,2) NOT NULL,
  stock INT DEFAULT 0,
  status TINYINT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (subcategory_id) REFERENCES subcategories(id) ON DELETE CASCADE
);

Best Practices

• Store relative paths (e.g., /uploads/products/img.jpg) for easy migration.
• Index foreign keys for faster joins.
• Use status instead of deleting records to keep history.
• Validate image uploads before saving to prevent broken links or malicious files.

This database structure keeps your data organized, easy to maintain, and ready to scale — whether you have a handful of products or millions.