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:
- 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
- Care needed when moving or removing categories
- Possibly more, which I'll add when I think of them...
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.
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
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).
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.
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.