Database choice for creating two connected tables?

Question

I have to database tables "Courses" and "Students".

Courses table has columns ("_id", "course_name", "course_number").
Student table has columns ("student_name", "student_number", "course_number").

I've created Courses table, but i have problem with creating Students table.

Two classes may have same student, so same student row will exist twice with different "course_number"s in Students table. I don't want replicate rows. In this way i can't also make "student_number" as primary key. Because it will exist twice for multiple classes. How to design this tables, i can't put multiple "course_number"s to same columns.

score 1 · Answer 1 · answered Mar 09 '15 at 16:00

1

Student (id, name, number) Course (id, name, number) StudentsCourses (student_id, course_id)

You have to make a many to many relation.

answered Mar 09 '15 at 16:00

sgpalit

2,676
2
16
22

NickJ · Accepted Answer · 2015-03-09T16:06:52.227

This is a classic case of many-to-many, and for that, you'll need a third table between Course and Student. The schema will look something like this:

Course table has columns ("course_id", "course_name")

Student_course table has columns ("student_id", "course_id");

Student table as columns ("student_id", "student_name")

Student_course table has foreign key constraints on both student and course tables.

Example data:

Course:

id   |  name
------------------
1    |  Maths
2    |  English
3    |  Science

Student

id   |  name
---------------
1    |  Tom
2    |  Dick
3    |  Harry

Student_course

student_id | course_id
------------------------
1          | 1
1          | 2
2          | 1
3          | 3

In this example, Student 1 (Tom) is on courses 1 and 2 (Maths, English),

Student 2 (Dick) is on course 1 only (Maths)

Student 3 (Harry) is on course 3 only (Science)

so _id is Primary key for "course", student_id is primary key for "student" tables ? Does student_course have to own primary key ? — Jemshit, Mar 09 '15 at 16:04
student_course has a composite primary key - course_id AND student_id — NickJ, Mar 09 '15 at 16:07

score 1 · Answer 3 · answered Mar 09 '15 at 16:02

There is m to n relationship between Courses and Students. To map a m to n relationship you need a third table.

courses_students
----------------
id_student
id_course

courses
----------------
id_course
// other fields

students
----------------
id_student
// other fields

Database choice for creating two connected tables?

3 Answers3

Linked