Cascade deletion

lpoht has asked for the wisdom of the Perl Monks concerning the following question:

I’m designing a website which will revolve around a database tree much like one discussed in this node. I will be using one table to track categories using nodeID, parentID, and Category fields.
For example:

nodeID  parentNodeID  Description
------  ------------  -----------
1       1             persons
2       1             kids
3       1             grown_ups
4       4             books
5       4             manuals
6       4             dictionaries

Another table will be for items within certain categories. I’m running into design issues when planning how cascading deletes will work. My thinking is a recursive function which takes as input a list of node IDs. It runs a db call to get all of the children of each of the nodes. If the nodes have no children, they are deleted. Otherwise, the function is called on the childrens’ node IDs. I’m a little worrisome of the time such a function would require to run. I realize that this isn’t a database design forum, but if anyone has ideas, I’m all ears. Also, if you know of any good resources for this sort of information, I’d love references. Thanks

Comment on Cascade deletion

Replies are listed 'Best First'.
Re: Cascade deletion by sauoq (Abbot) on Jun 08, 2003 at 03:55 UTC
My thinking is a recursive function which takes as input a list of node IDs. It runs a db call to get all of the children of each of the nodes. If the nodes have no children, they are deleted. Otherwise, the function is called on the childrens’ node IDs. I think you'll find it conceptually easier if the function just takes a single node ID. If you want one for lists, just write a wrapper that calls the recursive one once for each node in the list. Other than that, it seems entirely reasonable to me. One caveat: you'll need to be real sure you can't have any circular references. (It sounds like you won't easily be able to but you'll need to be careful if you allow node re-parenting.) -sauoq "My two cents aren't worth a dime.";	[reply]
Re: Cascade deletion by The Mad Hatter (Priest) on Jun 08, 2003 at 03:58 UTC
If you set up the relations right in Class::DBI, I believe it will do portable (aka. client side) cascaded deletes.	[reply]
Re: Cascade deletion by lpoht (Sexton) on Jun 08, 2003 at 03:51 UTC
I should have also mentioned that I want to write this code to be platform independent. I'm looking into using CASCADE options in SQL queries, but I'd like to be able to use this with ODBC, for example, which supports a limited set of SQL functions. Also, using related tables could lead to a massive number of tables, which would slow things down quite a bit on queries.	[reply]