NYCPHP Meetup

[Dan Cech]

Phil Powell wrote:
> I am having to create a cascade delete effect on table "department" that 
> is configured in the following fashion:
> 
> -- TABLE CONTAINING DEPARTMENTS FOR PERSONS
> create table if not exists department (
> id int not null auto_increment,
> primary key (id),
> department_name varchar(50) not null,
> department_parent_id int,
> unique_key varchar(20) not null,
> record_entered datetime
> );
> 
> department_parent_id is a "foreign key constraint" (table is MyISAM thus 
> no actual foreign key) set up to be recursively
> referring to another record (if not null) in the table.
> 
> I am having to delete a department; if I do so, I would naturally have 
> to delete all child records associated with the department.

snip

> What would then be your best recommendation, based upon the need and the 
> schema, for me to ensure that all related records are
> deleted?  I would prefer not to have to write a PHP script to have to do 
> so, but is that all I'm stuck with?

My recommendation is to look at using a more powerful structure to 
represent the relationship between your departments.  Specifically I 
would recommend Joe Celko's Nested Sets or a variation thereof.

You can find a lot of information in the archives of this list and the 
nyphp-dev list, and also plenty of resources on the net. A good place to 
start would be:

<http://www.intelligententerprise.com/001020/celko.jhtml>

This approach has many advantages, and would allow you to accomplish 
this type of delete quite efficiently, as well as offering additional 
ways to work with the data, such as a one-query answer to the problem of 
retrieving all ancestor or descendant departments.

Dan