Junction table question

F

Fred

I have been reading about using junction tables for tables that have
a many to many relationship.

In a lot of the examples they use a student table and a course table.
Then they create a junction table with the primary keys from each
table.

When a student enrolls in a course, is that when the junction table is
updated with a new row showing that student taking that course?

Thanks,
Fred
 
D

Dirk Goldgar

Fred said:
I have been reading about using junction tables for tables that have
a many to many relationship.

In a lot of the examples they use a student table and a course table.
Then they create a junction table with the primary keys from each
table.

When a student enrolls in a course, is that when the junction table is
updated with a new row showing that student taking that course?


Right.
 
S

Steve

To expand on Dirk's answer .........

In this example, a record only needs entered in the junction table if a
record for the student exists in TblStudent and a record for the course
exists in TblCourse.

Steve
 
K

Ken Sheridan

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
 
J

Johnathon Anderson

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

Yes thats much easier t understand ?
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

And thats - "mch easier" ?
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.

OMG ?

Come on Ken. If you are going to give an answer please do try and empathize
with the OP.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top