How to make correct table for category / subcategory / sub-sub
I have on my other projects category and subcatecory.
At this time I have done this by a separate table for cat and subcatecory like this:
category id catname subcategory id subcat_id subcat_name
In my actual project I have to do more than a subcategory like this.
Internet - WebDesign - Php Internet - WebDesign - Html House - Garden - Machines
How can I do this. I think its not good to make 3 tables with the categories , subcategorie, sub-subcategories
Thanks for any suggestions how is the best way to do this.
why not make a single table: cat_id, text, parent_id
if it is a top level category, parent_id is 0, otherwise parent_id is the cat_id of the parent. If you wanted a multi-lingual site, remove text column and have a seperate table with cat_id, language_id, text.
Normal way is to have a parent I'd e.g.
id category parent_id
1 cat 0
2 blackcat 1
3 dog 0
If you have well designed category ids, you might omit parent id e.g.
1133 pussy cat
113352 pussy cat golden
And here are some SQLs:
SELECT * FROM cat WHERE id LIKE '11%'; ## get all cats