Frank Stanovcak wrote:
> ""Boyd, Todd M."" <tmbo...@ccis.edu> wrote in message 
> news:33bde0b2c17eef46acbe00537cf2a19003cb4...@exchcluster.ccis.edu...
>> -----Original Message-----
>> From: tedd [mailto:t...@sperling.com]
>> Sent: Thursday, February 05, 2009 10:07 AM
>> To: php-general@lists.php.net
>> Subject: [PHP] Garbage Collection
>>
>> Hi gang:
>>
>> A related question to my last "Clarity needed" post.
>>
>> I have a tutor table (showing all the tutors), a course table
>> (showing all the courses), and a course-to-tutor table (showing all
>> the instances of what tutor teaches what course).
>>
>> Okay, everything works. Whenever I want to find out what courses a
>> specific tutor teaches OR what tutors teach a specific course, I
>> simply search the course-to-tutor table and bingo out pops the answer.
>>
>> Now, how do you handle the situation when a tutor quits or when a
>> course is no longer offered?
>>
>> If I search the course-to-tutor table for all the tutors who teach a
>> course and find a tutor who is no longer there OR search the
>> course-to-tutor table for all the courses a tutor teaches and find a
>> course that is no longer offered, how do you handle the record?
>>
>> I realize that if either search turns up nothing, I can check for
>> that situation and then handle it accordingly. But my question is
>> more specifically, in the event of a tutor quilting OR removing a
>> course from the curriculum, what do you do about the course-to-tutor
>> orphaned record?
>>
>> As I see it, my choices are to a) ignore the orphaned record or b)
>> delete the orphaned record. If I ignore the record, then the database
>> grows with orphaned records and searches are slowed. If I delete the
>> orphaned record, then the problem is solved, right?
>>
>> I just want to get a consensus of how you people normally handle it.
>> Do any of you see in danger in deleting an orphaned record?
> 
> tedd,
> 
> I believe relational integrity can solve your problem. In MySQL (and
> maybe MSSQL, but I am less "versed" with that product) you should be
> able to put a CASCADE option on the DELETE action for your tutor table
> so that when a record is deleted, its associated record in
> tutors-to-courses is also deleted. I'm assuming you would want to do the
> same for removing a record in tutors-to-courses when a course is removed
> (but not remove the tutor, the same as you do not remove the course
> itself when the tutor is deleted).
> 
> I suppose you could also do it yourself with PHP code when a failed link
> is turned up, but why bother separating DB logic from the DB itself? :)
> 
> HTH,
> 
> 
> // Todd
> 
> I agree with todd.  Set up the relationship so that when a record in one of 
> the two master tables is deleted the delete cascades to the linked table. 
> One to many forced update/delete I think it's called if you want to do a 
> search on it.
> 
> Frank 
> 
> 
Yes, and you haven't tried it, MySQL Workbench is very handy for
designing DBs.  It was Windows only, but they now have a beta out for
Linux and it seems to work fine.

-- 
Thanks!
-Shawn
http://www.spidean.com

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to