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.

8 Years
Discussion Span
Last Post by vee_liang
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.