CODEWITHSUNDEEP
sqlmany-to-many
user3637485
user3637485

Reputation: 1

SQL many-to-many tables relationship

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

Answers (2)

hacene abdessamed
hacene abdessamed

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

flowit
flowit

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

Related Questions