MySQL and multiple sub-categories

[Anon]

Hi there,
I have a categories database which should contain sub-sub-sub-categories,
something like:

Food
|
-------------Fruits
| |
| ----------Apple
| | |
| | -------------Granny
| | |
| | -------------Red
| |
| |
| ----------Lemons
| |
| |-------------yellow
| |
| -------------green
|
|-----------Vegetables...
|
|
|
Drinks
|
|

etc...
I need some nice advices to build up the categories table.
I have thought of 2 possibilities:
1) Table with ID, parent_ID and name
But it becomes a nightmare to use in terms of SQL statements to display the products
(I am then obliged to treat them one-by-one)

2) Table with ID main, ID sub1, ID sub2, ID sub3, name
Here it becomes more usable (can make general SQL statements),
but populating the categories table is a nightmare.

Any other idea?
If I had to choose beteween option 1 and option 2,
which one do you think is better/worse?

Thanks in advance!
Nobru

[trooper]

Norbu

You need to define your relationships between the product type and the product.

I would personally have one table with "food / drinks" then have another one with the actual product name and have the "product type be the id of either food or drink.

Make sure you create an index on the product type field as it will speed up searching

regards

Gary Mailer

[Anon]

First, thanks for the answer.

But I think my example (was only an example...) was not really well chosen.
Indeed, when I read my message again, I think it's a bit confusing...

What I meant is that I have sub-sub-sub categories, i.e.
main->sub-cat->sub-sub-cat->sub-sub-sub-cat
The products are in another table, and I make a lookup on the productID and categoryID.

My problem is "how to organize the category table so that it'll be easy to create and easy to maintain" (if it can ever be...).

Any idea?
Thanks again in advance.
Nobru