table `categories`

[phpsycho]

What would be the best way to do categories and multiple sub categories for a eCommerce website?

For example there could be...

Website templates => Flash => Portal => Music

Computer hardware => Gadgets => USB => Fun

Scripts => PHP => eCommerce => Simple

Scripts => Perl => Search engine => Simple

 

So something like that.. how should the database structure look?

 

Also say I have a table called 'products' and a column called 'cat'.. if the best idea would be to create `categories` `sub1_cat` `sub2_cat` etc for tables then should I put each id of the categories into the column 'cat' and separate by comma? Then like explode that column when I need to grab each category or whatever?

I dunno... lol very confusing, I just can't wrap my head around it.

[phpsycho]

Like I said..

I could just create like 5 tables (subcat1,subcat2,subcat3, etc) and then just add each id from each of those tables to the product table column `cat` and when needed I could explode or something.. dunno how I'd pull specific products from each sub category though using a sql query.

Theres gotta be a better way... I just can't seem to find it.

[fenway]

I like nested sets -- but they're slightly annoying to maintain (parent, depth, ancestor, etc.).

[phpsycho]

Could you give an example?

Sorry lol I feel dumb, but I don't really know what you mean by a nested set.

 

hmmm any other ideas then maybe? I'm looking for the most simple, but works perfectly. I suppose I could use a pain in the ass method if I had to, but yeah.. rather not lol.

[fenway]

Closure tables are a less painful option.