Database design of a tree-like category system

Question

I'm using the Adjacency List Model to create categories, and it works perfectly.

When retrieving articles in a certain category (for example electronics), I would like to also retrieve the articles in the sub categories (for example electronics->cameras, or even electronics->cameras->camera lenses).

The way I am doing it now is pulling from the DB all the category id's of the sub categories of electronics, and finding all articles with a category_id in this list.

This seems to me very inefficient and time-consuming, since this could result in many queries to retrieve these sub categories.

Another way I thought of doing this is having every article associated with the whole category tree (for example an article about camera lenses will also be associated with the camera and electronics categories in MANY_MANY table), and when I retrieve all articles in electronics it will also appear.

This would add a lot of redundant data to the database though, as I might have to store 3 or 4 categories for each article. Also, it would complicate actions like moving an article to another category.

Is this the right way to go? Or is there a better/simpler way that I have not thought of?

Any help appreciated!

Answer 1

Have a read of this article about Nested Set Modelling: Managing Hierarchical Data in MySQL.

Using the suggested technique, you can get entire trees or subtrees in one single SELECT. It's a bit more complicated than the "normal" approach, but it's totally worth it if you're going to be doing lots of reads from the table.