Database design for 3 strong entity sets

Question

I'm trying to design a database for 3 tables: Teacher, Course, Student.
- A teacher can have many students and can teach many courses.
- A student can have many teachers and study many courses.
- A course can be taught by many teachers and can have many students enrolled.
I want to be able to determine that which student is studying which course, and is taught by which teacher.

You might want to use switching tables, that only has foreign keys to the entity tables... — Usagi Miyamoto
@UsagiMiyamoto yeah...i thought abt that too...but wont that mean the table will be too big, thus may result in low query performance. — Acoustic Mike

Usagi Miyamoto Usagi Miyamoto · Accepted Answer · 2017-09-09T04:59:50

CREATE TABLE student (
  id   serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  -- other columns, constraints, etc...
);
CREATE TABLE teacher (
  id   serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  -- other columns, constraints, etc...
);
CREATE TABLE course(
  id   serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  -- other columns, constraints, etc...
);
CREATE TABLE student_course (
  student_id integer NOT NULL REFERENCES student(id),
  course_id  integer NOT NULL REFERENCES course(id),
);
CREATE TABLE teacher_course (
  teacher_id integer NOT NULL REFERENCES teacher(id),
  course_id  integer NOT NULL REFERENCES course(id),
);

SELECT s.id, s.name, c.id, c.name, .id, t.name
FROM student s
  JOIN student_course sc ON s.id = sc.student_id
  JOIN course c ON sc.course_id = c.id
  JOIN teacher_course tc ON c.id = tc.course.id
  JOIN teacher t ON tc.teacher_id = t.id

Database design for 3 strong entity sets

1 Answers