NYCPHP Meetup

[Phil Powell]

Dan Cech wrote:

> 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
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>
Interesting.. I'll look into that more, but the article does not 
indicate whether this approach works for a very simple MySQL 3.23 model 
MyISAM table.  If it doesn't implementing this would be fine, provided 
we tell every client that looks into our custom-built app to upgrade 
everything from PHP and MySQL on down before they can use our app they 
bought.

Phil

-- 
Phil Powell
Web Developer
  ADNET Systems, Inc.
  11260 Roger Bacon Drive, Suite 403
  Reston, VA  20190-5203
Phone: (703) 709-7218 x107   Cell: (571) 437-4430   FAX: (703) 709-7219
EMail:  Phillip.Powell at adnet-sys.com      AOL IM: SOA Dude