Cosmin Nicula

There is a time in your project when you need to represent some hierarchical data and the database engine happens to be MySQL and not MSSQL which by the way supports recursive CTEs! and recursive functions.
Now, there is a simple and easy way to represent the hierarchical data, which is fast at insert/update operations and slow at select operations (recursion bleah!) :

Category(
id    INT, PRIMARY KEY, AUTO INCREMENT
name    VARCHAR(50), NOT NULL
parent  INT, NULL
)

..and there is the opposite of the first solution (no recursion, fast select):

Category(
id    INT, PRIMARY KEY, AUTO INCREMENT
name    VARCHAR(50), NOT NULL
lft  INT, NOT NULL
rgt  INT, NOT NULL
)

I will concentrate on the second solution, and just to be sure you get the idea, I will attach an image with a full structure of categories:

The blue text represents the depth of the category and the red text represents the lft and rgt database fields. The green text represents the number of products in the category. You’ll see that if “Portable electronics” has 4 products, the sum of it’s child categories must be equal to 4 (“MP3 Players” + “Flash” + “CD Players” + “2-Way Radios”).
Before you choose the second solution to implement, you should be aware of the complexity of the queries and the conditions for the CRUD operations.
A quick look at the:
- update condition, when moving a category in another category (PHP syntax):

- select statement for categories and number of products (MySQL sytanx):

SELECT
`parent`.`id`,
`parent`.`name`,
`parent`.`visible`,
(
SELECT
COUNT(id)
FROM
categories
WHERE
rgt = lft + 1
AND id = parent.id
)AS is_leaf,
(
SELECT
(COUNT(parent_sub. NAME) - 1)
FROM
categories AS node_sub,
categories AS parent_sub
WHERE
node_sub.lft BETWEEN parent_sub.lft
AND parent_sub.rgt
AND node_sub.id = parent.id
GROUP BY
node_sub.id
ORDER BY
node_sub.lft
)AS depth,
COUNT(products.id)AS products_count
FROM
`categories`
CROSS JOIN .`categories` AS `parent`
INNER JOIN .`products`
INNER JOIN `categories_products` ON products.id = categories_products.product_id
WHERE
(
categories.lft BETWEEN parent.lft
AND parent.rgt
)
AND(
categories.id = categories_products.category_id
)
AND(categories.store_id = 1)
GROUP BY
`parent`.`id`,
`parent`.`visible`
ORDER BY
`parent`.`lft` ASC
LIMIT 100

and the result..

…and if you want to wrap it nicely, in a tree control, it should look like this:

So the conclusion (from the perspective of a .NET / C# developer and a PHP / Zend Ninja wannabe ? Doesn’t matter if you work with MySQL, SQL Server, PHP, Zend, .NET C#, you can always find a way to get the best of it.