> > 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? > > Cheers, > > tedd > I guess that all depends. If you want some kind of historical log of what tutor taught which course and which courses have previously been offered then I wouldn't delete the records. Instead I would and something like a "Active" column and use simple "Y" and "N" vaules to mark each tutor or course as active and then just re-write your query to only pull tutor's/courses with the "Y" flag. That would give you a current listing of active courses and who teaches them, and also retain the historical data if it need to be referenced later. If you don't care about historical data, you could just do a DELETE FROM my_table where course/tutor id = "xxxxx" But I would recreate an index on the table after deletion of records to keep the speed crisp. Dan
