Between Adjacency List Model and Nested Set Model

There is a time in your project when you need to represent some hierarchical data in the database and it happens to use MySQL – and not MSSQL – which BTW 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!). Let’s take a look at its structure:

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 focus on the second approach, and just to be sure you get the idea, I will attach an image representing some hierarchical data that we’ll be able to query:

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 choosing between the two approaches, you should be aware of the complexity of the queries and the conditions for the CRUD operations. Below you can find some examples :
– 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-like HTML control, you could end up having something like this:

Happy coding!