Efficient Strategies for Deleting Descendant Rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Efficient Strategies for Deleting Descendant Rows

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Efficient Strategies for Deleting Descendant Rows

Post 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
Post Reply