Categories and sub-categories, best practice

[MondeoST24]

I have an application which has a database table called Category. Its very simple, with fields id and categoryname.

My client now wants functionality for sub categories. Whats the best way to do this. My initial thought was to keep the same database table but have a 3rd column called ParentID which contains the ID of the parent category.

Is this the best way? Or something else.

Thanks

[psychotomus]

FORUM_CATS: cat_id, subcat_id, cat_title

then something like FORUM_POST: POST, CAT_ID, ect., ect.

[Ellis Dee]

I have an application which has a database table called Category. Its very simple, with fields id and categoryname.

My client now wants functionality for sub categories. Whats the best way to do this. My initial thought was to keep the same database table but have a 3rd column called ParentID which contains the ID of the parent category.

Is this the best way? Or something else.

ThanksAs the previous poster so cryptically explained, you should use a second table for the subcategories that relates back to the category table. That's the normal way to design a relational database.

Your original table remains unchanged:

tblCategory
CategoryID (Long) <== Primary key
Descrip (String)

tblSubCategory
SubCategoryID (Long) <== Primary key
CategoryID (Long) <== Foreign key; relates back to tblCategory
Descrip (String)

[psychotomus]

my way is perfectly normal. Many php forums do it the way i do it.

[Ellis Dee]

my way is perfectly normal. Many php forums do it the way i do it.As far as I can tell, we are both recommending the same approach. I was just offering more detail.

EDIT: On re-reading, I now see that you recommended a flat-file approach. I'm not a fan of that, no matter how many php forumns do it.

[psychotomus]

I didnt recommend a flat-file approach. i recommend 1 table instead of 2.

[Ellis Dee]

That's what flat-file means.

[Al42]

And it's not normalized, which is bad coding practice.

[Musician]

If you will only have one level of sub category then a second table is best. If you plan to have sub-sub categories and so on I think the parentID idea works better as it will alow and amount of sub levels.

[HoraShadow]

I try to avoid recursive structures. I would use the two entities approach.

HoraShadow

[Musician]

I don't think I was suggesting you do otherwise. If you want to have an unlimited number of sub categories though you have no choice.