I have a model/table Categories that have a Foreign Key to itself.

I have a model/table Products. Each Product can have multiple categories and each category can have multiple products.

Category Table

id | name | parent_id

1    name1   null
2    name2   1
3    name3   2 
4    name4   1
5    name5   4
6    name6   null

Cat-Prod Relation Table

id | name | product_id | category_id

1    name1   1            1
2    name1   1            2
3    name2   2            1
4    name3   3            1
5    name4   4            2
6    name5   5            2
7    name6   6            3

Products Table

id | name 

1    name1   
2    name2   
3    name3   
4    name4   
5    name5   

If I want to get all products that 'are' in category with id 1 (including subcategories) I need to get products from 1 and 2, 3, 4, 5 because they are children, grand_children of 1.

If I link/add a product to a category (ex:5), what is the best approach?

1. every time I add a product to a subcategory add it also to the parent, grandparent etc

ex- add it also in 4 and 1 - this adds a lot of rows in database for the product category relation, but needs one query to retrieve products.

2. add link just to 5 and use recursive

just one row in database, but many calls to a db at retrieval.

3. another approach

I need also to take in consideration that a Product can be in multiple categories.