one table or two??

johnie

Hi. I want to have an application with several main categories and several level of subcategories. Is it better to have all the categories in one table and have 2 fields (id, parent_id) OR have one table for main categories and another with subcategories?

Currently i'm using the second way but i can't figure out how to use sub-main categories and i'm thinking of switching to one table.

However, i was using the one-table way some months ago for another application, and i was forced to use 2 tables for a reason i can't remember 🙂

Thanks

John

PHPdev

Personally I think it depends on how many main catagories and sub- catagories you intend to have. If we are talking main catagores between 100-500, each having 1-X sub-catagories, then I would sudgest two table method. But if you are only going to have 20 mains and 5 subs for a total of 100 records, then just use the one table method.

I know we have no way of knowing what will be in the future, but in this case it is a judgement call.

PHPdev

johnie

Thanks for the answer.
My main concern is not the normalization of the db (or the size, there will be around 50-60 categories), but how to relate the parent categories with the sub-parents and the rest of the categories. That is why i think that one table is better. I don't know what problems i will face but it's not a problem since i can change it easily.

With one table i can have an id field and a parent_id field that will point to the id to know where it belongs to. And this can go up to any level you want.

How can i do this with 2 tables?

Thanks

John

examancer

to do this with two table's the first tabel must assign an id to each main catagory, while the second table only has to have a parent_id field, and doesn't need an id field (although using one won't hurt anything, if it makes updating easier for you).

PHPdev

Use the Key fields in main catagories table to relate the the sub catagoires...

table 1

id main_cat

1 cat1
2 cat2

table 2
id table1_id sub_cat

1 1 scat1
2 1 scat2
3 1 scat3
4 2 scat4
5 1 scat5

Let me know if this helps, or you have any questions.

PHPdev

Eep__

Or you can use 3 tables:

main (cross-refererence)

[u]id categoryID subcategoryID[/u]
1  1             2
2  2             3

johnie

Thanks guys.
My question is more general.
It's about having several main categories with each one having different levels of subcategories like Yahoo. For example the first main category has 3 levels of subcategories and the second has 5 levels and so on.

The best solution i can think is using only one table.
Of course it can use 3 tables (main, sub-mains, sub-categories) but it would be really difficult to code for these tables.

Someone can get lost in all these categories 🙂

Thanks

John

Eep__

Well, it depends if the categories are ALL unique or not. Regardless, you'll be duplicating data with a single table. Depending on how many top-level categories you want, they should not have a parentID, but the subcategories will:

[u]id category parentID[/u]
1   produce 0
2  fruits 1
3  vegetables 1
4  dessert 0
5  cake 4
6  candy 4

As you can see, it can get fairly complicated even with just a single table.

I've seen a webpage that explained single-table categories but I can't seem to find it right now, sorry.