Frank Stanovcak wrote: > ""Boyd, Todd M."" <tmboyd1@xxxxxxxx> wrote in message > news:33BDE0B2C17EEF46ACBE00537CF2A19003CB423F@xxxxxxxxxxxxxxxxxxxxxxx >> -----Original Message----- >> From: tedd [mailto:tedd@xxxxxxxxxxxx] >> Sent: Thursday, February 05, 2009 10:07 AM >> To: php-general@xxxxxxxxxxxxx >> Subject: 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
