creating flexible categories and unlimited subcategories

[johnmerlino]

Hey all,

 

If I want to have a category which can have subcategories, which in turn can have its own subcategories or belong to the parent category, and the subcategories can have more than one parent categories, is the most effective way to design this is to create a categories table and categories_join table where the categories table has a foreign key categories_join_id linking it to the categories_join table and the categories_join table has a field called parent_id which associates with the primary key of the categories table, therefore allowing me to have multiple subcategories to one category:

 

categories_join table

id parent_id

1 2

1 3

 

categories table

id categories_join_id

1 1

2 2

3 3

 

So from the example above, the first category has two parents, category 2 and category 3.

 

As an alternative option, in this post:

 

http://stackoverflow.com/questions/5384183/database-design-question-categories-subcategories

 

The second answer down mentions  to use recursive programming? But he doesn't give an example of what he means. Is he saying you have a function call itself passing it parameters as to what the parent should be:

 

function getParent($category,$parents = array()){

}

 

Thanks for response.