Re: Garbage Collection

Dan Shirah wrote:

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.

IMHO forget the active flag, replace it with a field "deleted" which is a timestamp, then you've got an audit trail of when the it was removed :) infact often seen three fields on every table, "inserted, updated and deleted" all timestamps and self explanatory. regards!