Reputation: 1
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?
Upvotes: 0
Views: 242
Reputation: 559
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
Upvotes: 0
Reputation: 1442
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.
Upvotes: 1