MySql closure table won't support duplicate sub-category for different parents

Question

I have been going around in circles with this closure table for awhile. The problem I have is with the second occurrence of a descendant. I have instances of sub-categories that appear in more than one parent category. I have reverted to this example for simplicity:

drop table if exists closure;
drop table if exists nodes;

create table nodes (
node int auto_increment primary key,
label varchar(20) not null
);

insert into nodes (node, label) values
(1, 'rootree'),
(2, '1stbranch'),
(3, 'midbranch'),
(4, 'corebranch'),
(5, 'leafnodes'),
(6, 'lastbranch'),
(7, 'lastleaf');

create table closure (
ancestor int not null,
descendant int not null,
primary key (ancestor, descendant),
foreign key (ancestor) references nodes(node),
foreign key (descendant) references nodes(node)
);

insert into closure (ancestor, descendant) values
(1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7),
(2,2),
(3,3), (3,4), (3,5),
(4,4), (4,5),
(5,5),
(6,6), (6,7),
(7,7);

Using the folowing query, I can get the desired results:

select group_concat(n.label order by n.node separator ' -> ') as path
from closure d
join closure a on (a.descendant = d.descendant)
join nodes n on (n.node = a.ancestor)
where d.ancestor = 1 and d.descendant != d.ancestor
group by d.descendant;

RESULTS:

rootree -> 1stbranch
rootree -> midbranch
rootree -> midbranch -> corebranch
rootree -> midbranch -> corebranch -> leafnodes
rootree -> lastbranch
rootree -> lastbranch -> lastleaf

But if I add another child, a child that already exists, for instance, I want to make leafnodes a child of roottree -> lastbranch -> lastleaf

I insert two new records into the closure table: (6-5) and (7-5)

Then all hell breaks loose. The I have tried everything I can think of but I'm not getting anywhere.

Incongruous

No. That just messes it up in a different way – Incongruous

My previous comment was a response to someone asking if I shouldn't have inserted (5-6) and (5-7) instead. I don't know why it disappeared, but I promise I wasn't just talking to myself – Incongruous

Answer 1

I found the answer here: Converting the Closure Table from a Weak Entity

ANSWER: I added two fields to the closure table to hold the id of both the ancestor and descendant. Now the nodes are independent of the actual values. The structure remains connected and is used for inserting, deleting, querying, etc., but the values that are retained at each node are their own entity and can be replaced by any other without affecting the structure of the tree. This allowed me to solve my particular problem, which was using the same child category in multiple parent categories. I expect that there are many other benefits, yet to be discovered.

Incongruous

Dont' quite understand your answer. The link you posted talks about creating an assoc. table between the base and closure. But you're talking about adding ancestor&descendant id's to closure, which it already has.(?) – Benbow