tedd wrote:
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?
So Joe quits - he has a "finishdate" or similar flag to say he's not
attending the course any more.
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?
Mark the course as "not available" or something like that but I wouldn't
delete it. Like you said, you lose all of the history if you did that.
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?
The problem there is "oops typo" - now you either have:
- a lot of secondary records to update (redundant data)
- have to re-add everyone to the new course.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
