MySQL & PHP - Creating Multiple Parent Child Relations
- by Ashok
Hi, I'm trying to build a navigation system using categories table with hierarchies. Normally, the table would be defined as follows:
id (int) - Primary key
name (varchar) - Name of the Category
parentid (int) - Parent ID of this Category referenced to same table (Self Join)
But the catch is that I require that a category can be child to multiple parent categories.. Just like a Has and Belongs to Many (HABTM) relation.
I know that if there are two tables, categories & items, we use a join table categories_items to list the HABTM relations. But here i'm not having two tables but only table but should somehow show HABTM relations to itself. Is this be possible using a single table? If yes, How? If not possible, what rules (table naming, fields) should I follow while creating the additional join table?
I'm trying to achieve this using CakePHP, If someone can provide CakePHP solution for this problem, that would be awesome. Even if that's not possible, any solution about creating join table is appreciated. Thanks for your time.
