DB structure help

[MarioPro]

Hi,

I was wondering, and thanks in advance for your comments, what is the best way to make the database tables in order to speed up and drive it effectively on user searches on the following example.

I have a kind of hierarchy for keywords, i.e.:
Animals
Animals > Livestock
Animals > Livestock > Cow
Animals > Livestock > Cow > Beef cow
Animals > Livestock > Cow > Beef cow > Black Angus
Animals > Livestock > Cow > Beef cow > Charolais
Animals > Livestock > Cow > Dairy cow
Animals > Livestock > Cow > Dairy cow > Guernsey
Animals > Livestock > Cow > Dairy cow > Jersey
Animals > Pet
Animals > Pet > Reptile
Animals > Pet > Reptile > Snake
Animals > Pet > Reptile > Turtle
Boats
Boats > Fishing
Boats > Fishing > Sail
Boats > Fishing > Motor
Boats > Leisure


(...) etc.

Now, should I group all in just one table or build a seprate table for each branch or sub-categories? Like:

1 | 0 | 0 | 0 | Animals
1 | 1 | 0 | 0 | Livestock
1 | 1 | 1 | 0 | Cow
1 | 1 | 1 | 1 | Beef cow
1 | 1 | 1 | 2 | Dairy cow
1 | 2 | 0 | 0 | Pet
1 | 2 | 1 | 0 | Reptile
2 | 0 | 0 | 0 | Boats
2 | 1 | 0 | 0 |Fishing
etc.

or just
Main cat table
1 | Animals
2 | Boats

Second cat table
1 | 1 | Livestock
2 | 1 | Pet
3 | 2 | Fishing

Third cat table
1 | 1 | 1 | Cow
2 | 1 | 1 | Goat
3 | 1 | 2 | Snake
4 | 2 | 1 | Sailing

Thanks for any help

[raf]

well, the best sollution would be an hierarchical db instead of a relational-db...

these sort of taxonomies have 1 big disadvantage: you can only place each member at one position.
the approch of working with labels (like for instance google in gmail is a lott more flexible and in my opinion, faster to search on).

But it realy depends on what you wanna do with your db. I can image it's used for more then just searches and even the way in which you wanna search it will determine what the best db-design is.

[MarioPro]

Thanks raf for your reply.

I've been researching for the advantages of hierarchical versus relational for my purpose and as far as I got, it seems that I found that relational would be better, but after your reply, I started wondering again.

The answer I found was with the following structure:

TABLE CATEGORIES
catID | cat_name

TABLE SUB-CATEGORIES
sub_catID | sub_cat_name

TABLE CATEGORIES RELATION
cat_ID | sub_catID

...and so on.

Why? Because some sub-categories might fall within more than one main category, and the same for the sub-sub-categories.

Well, I don't know if in this case this would be the correct approach, but at least I wouldn't have duplicate names and would be able to best join tables for multiple results and even for possible breadcrumbs.