I have the following scenario: a student can enroll to more courses and on a cours can be enroll more different students. What tables and how should I create for this to work properly?
Asked
Active
Viewed 169 times
0
-
You need an additional table that holds a course id and a student id. This way you can store all the relationships. – Siderite Zackwehdex Apr 19 '16 at 13:32
-
Thank you Siderite Zackwehdex. I got it. – user3637485 Apr 19 '16 at 13:36
2 Answers
1
It's called pivot table. All you need are the primary keys from the participating tables, in SQL in would look like this:
CREATE TABLE student (sid INTEGER PRIMARY KEY, name VARCHAR(100));
CREATE TABLE course (cid INTEGER PRIMARY KEY, desc TEXT);
CREATE TABLE enrollments (sid INTEGER, cid INTEGER, edate DATE, PRIMARY KEY (sid,cid), FOREIGEN KEY sid REFERENCES student(sid), FOREIGEN KEY cid REFERENCES course(cid));
As you see, you can even add more columns to the pivot table, like I did with the enrollment date.
flowit
- 1,382
- 1
- 10
- 36
-
That's not a pivot table. It has many names - see http://stackoverflow.com/questions/3045034/whats-the-correct-name-for-a-association-table-many-to-many-relationship but never heard it called pivot. – David Glickman Apr 19 '16 at 13:44
0
you should create another table (association table) that contains at least course_id and student_id . these two columns are foreign keys that refer to tables course and student repectively
hacene abdessamed
- 559
- 6
- 22