Php – MySQL & PHP – Creating Multiple Parent Child Relations

cakephpjoin;MySQLPHP

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.

— Edit —
My question seems to be a bit confusing, so I'm trying to restate what I'm looking for.
In traditional self referenced (self join) parent-child relations, each item can have only one parent. What I'm looking for is to simulate a HABTM relation i.e. multiple parents for each item.

Categories & Items – To define HABTM, we use categories_items join table.

If within Categories I need HABTM, what should I do?

Best Answer

I hope it isn't bad form to answer a second time, having misunderstood the question the first time. The following is essentially a CakePHP implementation of pinkgothic's answer.

New HABTM join table:

CREATE TABLE `categories_parent_categories` (
  `category_id` int(10) unsigned NOT NULL,
  `parent_category_id` int(10) unsigned default NULL,
  `order` int(10) unsigned NOT NULL default '0'
);

Association in model:

class Category extends AppModel
{
    var $hasAndBelongsToMany = array(
        'ParentCategory' => array(
            'className'             => 'Category',
            'joinTable'             => 'categories_parent_categories',
            'foreignKey'            => 'category_id',
            'associationForeignKey' => 'parent_category_id',
            'order'                 => 'CategoriesParentCategory.order'
        )
    );
}
Related Topic