table structures for directories

Hi all,

i'm looking for conceptual advice here - how do you represent up a group of categories, with subcategories, and subsubcategories etc

e.g.

animal
cat
dog
labrador
alsation

I'm looking for the best way to store this data, so that if you know the category is, say, alsation, you can clearly see it is a subcategory of dog, and animal

I had an idea that there would be two tables

--------------
| 1 | animal |
| 2 | cat |
| 3 | dog |
| 4 | labrador|

and another table, with columns category ID, parent1ID, parent2ID

so for labrador, it would go

categoryID | parent1ID | parent2ID
---------------------------------------
4 | 1 | 3

does that sound a sensible way to do this, or is there a better way?

Thanks

 

category_id	category_name	parent_category_id

1		animal		0
2		cat		1
3		dog		1
4		labrador	3

Code (markup):

 

wordpress categories use this kind of logic..

have parent as 0 for the top most cats..