Foreign Keys - Beginner Question

[BertinaH]

I have a problem understanding foreign keys.

There are two tables: say Students and Courses.

The tables have a many-to-many realtionship. i.e students can take 0 or more courses and courses can have 1 or more students.

In many-to-many relationships: does it mean there will always be two foreign keys? I.e. one foreign key in Courses table and another foreign key in the Students table?

[Stephen]

with 2 tables (student and course) you cannot have a many to many relationship unless you have include a column in each table for each relationship (e.g. in Student you would have CourseID1, CourseID2, ...). This would be very difficult to manage and use and I would discourage it.

A much better option is to create a third table that contains a single row for each course a student is enrolled in (e.g. StudentCourse with two columns StudentID and CourseID). This table would have a FK back to the relevant table and possibly a unique constraint on the two columns (which would probably be the primary key anyway).

If a student can enrol in a course more than once, then you may need to include another column (perhaps course commencement date) so that you can uniquely identify each record.

[MAK]

In Simple SQL terms

Table: Course
CourseID (PK)
CourseDescription
type
CourseFee
Duration


Table: Student
StudentId (PK)
StudentName
DOB
Address
city
zipcode

Table: CourseProcess (composite PK-studentid,courseid,joindate)
StudentID (FK of student.studentid)
CourseID(FK oof course.courseid)
JoinDate

[BertinaH]

Thank you for your posts.

From what I have read here, and I may have misunderstood, I should create a third table.

The table Student_in_Courses will sit between the Student and Courses tables.

The Student table is the parent and Courses table is the child.

The Student table has attributes:
StudentID [PK]
StudentName

The Courses table has attributes:
CoursesID [PK]
CoursesName

The Student_in_Courses table will hold:

StudentID [PK][FK]
CoursesID [PK][FK]


Would this be correct?

[MAK]

Thats is what I did.

your "Student_in_Courses" = My "CourseProcess"