MySQL Category Tree

Database and Table structure

Looking at different tutorials and open source projects on the net there's a number of different ways in which category trees are managed. Some I like, some... not so much. In this article I'll cover the method I'm currently using. Firstly, the Pros and Cons:

Pros

  • Fast, simple queries to build the path of a category
  • A few simple statements to add categories or products
  • Fast and easy to build complete category trees

Cons

  • Care needed when moving or removing categories
  • Possibly more, which I'll add when I think of them...

The Tables

We'll be using InnoDB for the tables (this should work with other engines, but check the INDEXs). Firstly, we'll start with the categories table, which stores the information for each category.

`categories` example row
id id_parent node_depth node_path title
7 6 3 0,1,6,7 Snakes
CREATE TABLE `categories` (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	
	id_parent INT UNSIGNED DEFAULT 0 NOT NULL,
	
	node_depth TINYINT UNSIGNED DEFAULT 1 NOT NULL,
	node_path VARCHAR(255) DEFAULT '0' NOT NULL,
	
	title VARCHAR(32) DEFAULT '' NOT NULL,
	
	INDEX `idx_parent`(id_parent)
) ENGINE='InnoDB';

This table describes the categories; if you want to add icon image URLs, descriptions, etc, you'd do so here. Lets look at the table columns:

  • id - An automatically generated unique ID for each entry
  • id_parent - The ID of the parent category (or 0 if this is a top-level category)
  • node_depth - The distance from the root node (0) of this category
  • node_path - The IDs (including 0, root) of each ancestor category in ascending order. Order by this column to sort by ancestor path
  • title - The title text for this category
  • INDEX idx_parent - An index to speed up lookups for categories that belong to a specific parent
`category_parent` example rows
id_category id_parent
7 6
7 1
7 0
CREATE TABLE `category_parent` (
	id_category INT UNSIGNED DEFAULT 0 NOT NULL,
	id_parent INT UNSIGNED DEFAULT 0 NOT NULL,
	
	INDEX `idx_category`(id_category)
) ENGINE='InnoDB';

This table connects each category by its id, id_category, to the IDs of its ancestors (id_parent). There is one entry for each ancestor category, including root (0).

`products` example row
id title id_parent
5 Duke 5
CREATE TABLE `products` (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	
	title VARCHAR(32) DEFAULT 'Example' NOT NULL,
	
	id_parent INT UNSIGNED DEFAULT 0 NOT NULL,

	INDEX `idx_parent`(id_parent)
) ENGINE='InnoDB';

We're using this table as an example of how we can attach items to our categories. The products table has two columns we're particularly interested in and which are required; id, the unique ID for each row, and id_parent, the ID of the parent category.

`product_parent` example rows
id_product id_parent
5 5
5 2
5 1
5 0
CREATE TABLE `product_parent` (
	id_product INT UNSIGNED DEFAULT 0 NOT NULL,
	id_parent INT UNSIGNED DEFAULT 0 NOT NULL,

	INDEX `idx_product`(id_product)
) ENGINE='InnoDB';

The product_parent table connects a product by its ID, id_product, to its parent category and each ancestor of the parent by their ID, id_parent.

We've also added some indexes to each table to speed up queries. Next, we'll look at some common queries for modifying the tables.

Page loaded in 0.013 second(s).