What the best way to handle categories, sub-categories - hierachical data?

Question

Duplicate:

SQL - how to store and navigate hierarchies

---

If I have a database where the client requires categories, sub-categories, sub-sub-categories and so on, what's the best way to do that? If they only needed three, and always knew they'd need three I could just create three tables cat, subcat, subsubcat, or the like. But what if they want further depth? I don't like the three tables but it's the only way I know how to do it.

I have seen the "sql adjacency list" but didn't know if that was the only way possible. I was hoping for input so that the client can have any level of categories and subcategories. I believe this means hierarchical data.

EDIT: Was hoping for the sql to get the list back out if possible

Thank you.

Answer 1

table categories: id, title, parent_category_id


 id | title | parent_category_id
----+-------+-------------------
  1 |  food |              NULL
  2 | pizza |                 1
  3 | wines |              NULL
  4 |   red |                 3
  5 | white |                 3
  6 | bread |                 1

I usually do a select * and assemble the tree algorithmically in the application layer.

Answer 2

You might have a look at Joe Celko's book, or this previous question.

Answer 3

creating a table with a relation to itself is the best way for doing the same. its easy and flexible to the extent you want it to be without any limitation. I dont think i need to repeat the structure that you should put since that has already been suggested in the 1st answer.

Answer 4

I have worked with a number of methods, but still stick to the plain "id, parent_id" intra-table relationship, where root items have parent_id=0. If you need to query the items in a tree a lot, especially when you only need 'branches', or all underlying elements of one node, you could use a second table: "id, path_id, level" holding a reference to each node in the upward path of each node. This might look like a lot of data, but it drastically improves the branch-lookups when used, and is quite manageable to render in triggers.

Answer 5

Not a recommended method, but I have seen people use dot-notation on the data. Food.Pizza or Wines.Red.Cabernet

You end up doing lots of Like or midstring queries which don't use indices terribly well. And you end up parsing things alot.