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.

Answers


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.


  1. Normal way is to have a parent I'd e.g.

    id category parent_id


    1 cat 0

    2 blackcat 1

    3 dog 0

  2. If you have well designed category ids, you might omit parent id e.g.

    id category


    11 cat

    22 dog

    1133 pussy cat

    113352 pussy cat golden

And here are some SQLs:

SELECT * FROM cat WHERE id LIKE '11%'; ## get all cats

Need Your Help

.net application object promoted to gen 2

c# .net windbg

I have been trying to fix memory leaks in my application for quite a while. Few months back, I noticed that in my application, more than 95% of the objects get promoted to Gen2; and I have some very

Unable to update embedded item in a mongo document (no method update)

node.js mongodb mongoose

I have a mongoose schema that looks like this, that I am able to update and query just fine: