Page 1 of 1

Efficient Strategies for Deleting Descendant Rows

Posted: Fri Dec 16, 2011 3:37 pm
by volomike
I've also asked this question here (http://stackoverflow.com/q/8540240/105539). I'm trying to look at strategies for deleting descendant rows from a single table that recursively points back to itself.

Imagine nothing but a single categories table with only:

* id
* parent_id
* category_name

The parent_id is where you point back to the id column of the categories table, but to a different row -- the parent of this category.

I want to delete a given category, but delete all its descendants (children, grandchildren, great grandchildren, etc.) as well.

Note I have PHP5, PDO, and MySQL 5.

Re: Efficient Strategies for Deleting Descendant Rows

Posted: Fri Dec 16, 2011 6:57 pm
by mikosiko
Hmm... an Adjacent List... hate that..but that is just me.... I saw that in the other forum somebody suggested you the alternative of use a Nested Set.... personally I like the "Closure Table" implementation better.

this presentation posted in other forum long time ago is a must to be read... around slide 48 and up apply to this kind of implementation, specifically slide 68 to 77 show and explain the third alternative "Closure Table"
http://www.slideshare.net/billkarwin/sq ... trike-back

hope that provide you some help