Sub Category System

[shaggycap]

I have searched high and low and wondered if anyone knew of any examples of the following:

I want to build a navigation category/subcategory system, for a content management system. I want to be able to add categories and sub categories into a database structure, and from this I want to be able to create nested <ul> 's.

I am at a loss as I just cannot seem to get my head around how I should create the database structure, as I want to be able to have a potentially unlimited number of sub categories.

Has anyone ever done anything like this?

Many thanks

[utexas_pjm]

Consider a structure like this:

[code]
category_table {category_id, category_label, parent_category}
[/code]

You can then print your navigation with a trivial recursive function.

[Eric_Ryk]

Are you looking for a way to have sub categories in sub categories?

If so take a look at this:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[Albright]

If your entries only need to go down a certain level of depth (say, only two levels; entries can have a "parent" and a "grandparent" but not a "great-grandparent"), it would be easier and faster just to add a "grandparent" column to the table in addition to a parent one. Then add a "great-grandparent" column if necessary, and so on.

[Eric_Ryk]

[quote author=Albright link=topic=118749.msg485668#msg485668 date=1166214158]
If your entries only need to go down a certain level of depth (say, only two levels; entries can have a "parent" and a "grandparent" but not a "great-grandparent"), it would be easier and faster just to add a "grandparent" column to the table in addition to a parent one. Then add a "great-grandparent" column if necessary, and so on.
[/quote]Thats a bad idea that takes up tons of needless space. If you look at the link I posted it's a much more elegant solution. The only time that I'd recommend doing that is for 1 tier of child to parent, and that's all.

[Albright]

Tons of needless space? Not necessarily, especially if the database supports short integers and the concept of "null." A little needless space, sure, but not "tons" of it. And with that method, you can also use simpler SQL calls, with no need to join upon join upon join.

[redbullmarky]

I'm with Eric 150% on this one, and the MySQL example is perfect. For a slightly more complex MySQL query, you're left with a completely reusable tree system that can be used for all sorts of things.
I know it's a different type of structure, but imagine MySpace or YouTube if they had an extra short integer for each of their members/videos, considering the number of members/videos they have? The key to good database design is not wasting space unnecessarily (even just a byte for each record), as it doesnt take long for things to get messy.

Also, consider the idea that once there is tonnes of data, you may want to categorise the parent categorys into a few 'master' categories - ie, give the parents new parents. That could potentially be a much bigger job in terms of converting the database and all the scripts to handle the new table structure. say you call them 'parent' and 'grandparent'. to add a further level up, you'd need field names like 'greatgrandparent' and 'greatgreatgrandparent' and so on.

LEFT JOINs can be a little intimidating the first time you use them, but it's very unlikely that if you move on to bigger and better stuff, you wont need them at all. They're both unavoidable and a god-send, IMO

So Albright - you're not completely wrong, and yes - things would be simpler to start with and it would work well. But doing a script in a 'reusable' and more structured way is going to save tonnes of headaches later down the line when it comes to either expanding or putting it to use in something else.

Cheers
Mark

[Eric_Ryk]

[quote author=Albright link=topic=118749.msg486010#msg486010 date=1166257794]
Tons of needless space? Not necessarily, especially if the database supports short integers and the concept of "null." A little needless space, sure, but not "tons" of it. And with that method, you can also use simpler SQL calls, with no need to join upon join upon join.
[/quote]In the thing that I posted there was no need to left join. The example that they showed with left joins was what you shouldn't do.

[redbullmarky]

even still - using JOINs on a well formed database is going to be much more scalable than the addition of a new field for each level. Both the examples form a well structured hierarchy.
Eric - thanks for the link though. Am trying to get my head around the latter example as it's pretty interesting.

[trq]

You might also take a look at implimenting nested-sets. [url=http://www.phpriot.com/d/articles/php/application-design/nested-trees-1/index.html]Here[/url] is a great tutorial I found on the subject quite a while ago.

[Eric_Ryk]

[quote author=thorpe link=topic=118749.msg486351#msg486351 date=1166312135]
You might also take a look at implimenting nested-sets. [url=http://www.phpriot.com/d/articles/php/application-design/nested-trees-1/index.html]Here[/url] is a great tutorial I found on the subject quite a while ago.
[/quote]The link I posted also goes to nested-sets, but you can never have too many references.