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.