3

I'm storing categories using a hierarchical model like so:

CATEGORIES
id | parent_id | name
---------------------
1  | 0         | Cars
2  | 0         | Planes
3  | 1         | Hatchbacks
4  | 1         | Convertibles
5  | 2         | Jets
6  | 3         | Peugeot
7  | 3         | BMW
8  | 6         | 206
9  | 6         | 306

I then store actual data with one of these category ids like so:

CARS
vehicle_id | category_id | name
-------------------------------
1          | 8           | Really fast silver Peugeot 206 
2          | 9           | Really fast silver Peugeot 306 
3          | 5           | Really fast Boeing 747
4          | 3           | Another Peugeot but only in Hatchbacks category

When searching for any of this data, I would like to find all child / grandchild / great grandchild etc. etc. nodes. So if someone wants to see all "Cars", they see everything with a parent_id of "Hatchbacks", and so everything with a parent_id of "Peugeot", and so on, to an arbitrary level.

So if I list a "really fast Peugeot 206" with a category_id of either 1, 3, 6, or 8, my query should be able to "travel up" the tree and find any higher categories which are parents/grandparents of that child category. E.g. a user searching for Peugeots in category "8" should find any Peugeots listed with categories 6, 3, or 1 - all of which category 8's descendants.

E.g. using the above data, searching for "Peugeot" in category 3 should actually find vehicles 1, 2 and 4, because vehicles 1 and 2 have a category ancestor trail which leads back up to category 3. See?

Sorry if I haven't explained this well. It's difficult! Thank you, though.

Note: I have read the MySQL dev article on hierarchies.

BigJeffrey
  • 141
  • 2
  • 9
  • 1
    This will get messy and clumsy with MySQL as it does not support hierarchical queries –  Apr 21 '11 at 20:24
  • @a_horse_with_no_name: [this article](http://dev.mysql.com/tech-resources/articles/hierarchical-data.html) seems to use the same storage methods as I'm using and looks like it works OK. Or am I missing something? – BigJeffrey Apr 21 '11 at 20:25
  • 1
    http://stackoverflow.com/questions/1085287/hierarchical-data-in-mysql does this help? – sdolgy Apr 21 '11 at 20:36
  • the gist of that article is not to use a "hierarchical" model but to use the nested set model (which does not require the DBMS to support hierarchical queries) –  Apr 21 '11 at 20:49
  • MySQL can support an Adjacency List model, see my answer. – orangepips Apr 26 '11 at 17:51

2 Answers2

1

Normalized models are great, but not when you actually have to query them.

Just store the "path" to your category in category table. Like this: path = /1/3/4 and when query you database like "select .... where path like '/1/3/%'" It will be much more simple and fast than multiple hierarchical queries...

Andrey Frolov
  • 1,534
  • 10
  • 19
0

You've represented your data as an Adjacency List model, whose querying in MySQL is best done using session variables. Now, this is not the only way you can represent a hierarchy in a relational database. For your particular problem, I would probably use a materialized path approach instead, where you do away with the actual categories table and instead have a column on your cars table that looks like Cars/Hatchbacks/Peugeot on a per record basis and use LIKE queries. Unfortunately that would be slow as the number of records grew. Now, if you know the maximum depth of your hierarchy (e.g. four levels) you could break that out into separate columns instead, which you allow you to take advantage of indexing.

Community
  • 1
  • 1
orangepips
  • 9,891
  • 6
  • 33
  • 57