Can't decide on Design for Simple Categories Structure

[Benzino]

Hey,

I'm totally new at database design, and can't for the life of me decide how to design my system.

I have categories, which contain sub-categories, and in these sub-categories are items. For example:

Wedding Music (category) ---> Bands(sub-category) ---> Awesome Band (item)

What I'm trying to do is achieve a similar category system to the one shown here http://www.weddingsonline.ie/

So I need to be able to view all items in a sub category, but also be able to view the all sub-category items when viewing the main category (wedding music in the above example). I hope I have explained myself clearly.

The way I was thinking of doing it was like so:

Have a main_category table with an id field. Have a sub_category table with an id field and a main_cat_id field (corresponds to the id field in the main_category table). Then have an items table with a main_cat_id and sub_cat_id fields (i have both so as to save on the amount of queries I need to do when I want to find all items in a main category).

But I have a feeling this is not efficient. I know it's a simple design to do, but I have no experience doing database design and would like to get it right.

So any help or suggestions would be greatly appreciated, thanks :)

I'm using PHP and MySQL by the way.

[vee_liang]

Are you talking about making a nested data set like in a daily example is organization chart? If so, you might wanna take a look at this tutorial. http://www.developer.com/db/article.php/10920_3517366_1/Using-the-Nested-Set-Data-Model-for-Breadcrumb-Links.htm

it is a technique that most people called it nested set data model. up to the time being it is the most efficient way to represent things like organization chart.

hope it helps.