What is a good way to design categories in a database?

[keldorn]

I'm able to easily create categories, but I run into problems trying to think of ways to create sub-categories of a category in a sql database, how can this be done in the most efficient way?

 

This is how I would do with no sub categories.  Say you an article table, that holds say articles.

 

Okay you have:

id+cat_id+content++++++++++++title

1+1------This is your first post+ Hello world

 

 

Then you have another table called categories, where you have this,

id+name

1+foo bar

 

Doing this you can easily after a mysql query take the cat_id, then say,

 

SELECT name FROM category_table WHERE id=1)

You will get Foo bar.

 

 

I belive this is called recurvsive naming, where the article table only holds the ID number of the category, which translates to name in category_table. But things to me, get more confusing when designing sub-categories. So what is the best way to design the tables using sub categories? 

[xtopolis]

This might address your question: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[keldorn]

This might address your question: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

Hey thanks for the link. Just what I need. I was looking over wordpress how they set it up, its much in the same way.

[keldorn]

The link didn't really help actually, I've spent the past 2 nights tyring to figure this out myself. (I love a problem).

So far I got this. Which works. I've added now a Depth to the table, Somehow I will use that to create a tree structure of  the whole parent node. haha I bet no one here can figure out this works? Its like almost like binary. 

 

 

[attachment deleted by admin]