> > Hi gang: > > To further the Garbage Collection thread on to another level (i.e., > preserving history) please consider this: > > Okay, let's say we have a table containing all the instances of tutors > teaching courses. A record might look like this: > > Course-to-Tutor table > course_id = 123 > tutor_id = 7 > date_active = 2/4/9 > date_inactive = null > > The above record was assembled from the following recrods: > > Tutor table: > id = 7 > name = joe > > Course table: > id = 123 > title = Introduction to Moonshine > > Okay, so let's now delete the course "Introduction to Moonshine"! > > At the first opportunity to search the Course-to-Tutor table we find that > the course 123 is now absent from our database and as such we set the > date_inactive field -- it won't be considered again. > > Okay, so what does that tell us about the history? It only provides that at > one time joe taught an undefined course. Furthermore, if joe quits, then we > only have a record that says someone with an id of 7 taught a course with an > id of 123 -- that doesn't make much sense, does it? > > Now, if we reconsider the Course-to-Tutor table and instead of putting in > the tutor_id and course_id, we put in the actual name of the tutor and title > of the class, then at least we have a history that makes sense. > > However by doing that, we violate the primary principle of a relational > database by creating redundant data. > > I guess that one can say that if we never actually delete the tutor nor the > course, but instead just make them inactive as well, then we have solved the > problem -- is that the answer? > > In any event, what problems do you foresee if I used the actual names and > titles for the Course-to-Tutor table instead of their id's? After all, the > instance records would retain a simple history of what happened regardless > of deletions AND a search for "Introduction to Moonshine" should be > essentially as fast as a search for "123" if both fields are indexed, am I > right or wrong? > > Cheers, > > tedd > You only need to use ONE value in both tables. tutor_table: tutor_id = 7 first_name = joe last_name = smith active_tutor = Y course_table: course_id = 123 tutor_id = 7 title = Introduction to Moonshine active_course = Y A crude query to return all active tutors and their courses would be: SELECT tutor_table.first_name, tutor_table.last_name, course_table.title FROM tutor_table, course_table WHERE tutor_table.tutor_id = course_table.tutor_id AND tutor_table.active_tutor = 'Y' AND course_table.active_course = 'Y' That will return the course title and tutor name for all active events Hope that helps. Dan
