Re: Preserving History

Dan Shirah schreef: >> 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' I would use a more generic field that 'active_tutor', et al something like 'state' ... and make it an ENUM field with some values which you could later extend, e.g. active deceased incapacitated inactive fired a different set of states could be defined for courses: active inactive deleted you might also consider some kind of transaction table to store state changes ... or defer to Nate Rixham about using geospatial indexes (storing id+timestamp as primary key ... thereby creating a new record for each change in the data) @Nathan ... sorry if I spelt your name wrong, it's late and a bottle of wine has been emptied. > > That will return the course title and tutor name for all active events > > Hope that helps. > Dan >