Fred:
You might find it easier to understand if you think of in terms of what the
tables are really modelling. Tables model entity types and their columns
model attributes of each entity type.. Students is an entity type with
attributes such as StudentID, FirstName, Lastname, DateOfBirth etc. Courses
is another entity type with attributes such as CourseID, CourseTitle etc.
A so called 'junction' table models the relationship type between the
Students and Courses entity types. But a relationship type is really a
special king of entity type so has its own attributes, in this case
StudentID, CourseID, EnrolmentDate etc. You might call this table
Enrolments, as that's what its modelling, an enrolment in a course by a
student being a relationship between the student and the course. The date
when the student enrols is an attribute of the relationship. So you are
quite right about this being when a row is inserted into the table.
On thing you might not realise is that its also perfectly feasible for a
one-to-many relationship type to be modelled by a table, not just a
many-to-many relationship type. Normally this would be modelled by a foreign
key in the referencing (many side) table referencing the primary key of the
referenced (one side) table of course, but there are occasions when its
appropriate to model the relationship type with a table in the same way as
with a many-to-many relationship table, and is specifically recommended by
Chris Date, one of the major figures of the relational database world, as a
means of avoiding Null foreign keys in situations where only a subset of rows
in a referencing table reference a referenced table. The difference in this
case is the foreign key column which references the referencing table in the
one-to-many relationship is indexed uniquely, rather than non-uniquely as
when a many-to-many relationship is being modelled.
Ken Sheridan
Stafford, England
