""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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
