School database schema: ER diagram and SQL

This is the classic many-to-many example: students take many courses, and each course has many students. It also shows when a junction table earns extra columns of its own.

Open this schema in the editor Jump to the SQL

Tables in this schema

teachers

Staff who teach courses.

courses

Classes on offer, each taught by one teacher.

students

People enrolled at the school.

enrollments

Which student takes which course, plus the grade. A junction table that carries data about the relationship itself.

How the tables relate

courses.teacher_id references teachers.id: one row in teachers can have many rows in courses.
enrollments is a junction table between students and courses: many-to-many.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE teachers (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  email varchar(255) NOT NULL
);

CREATE TABLE courses (
  id bigint PRIMARY KEY,
  teacher_id bigint NOT NULL REFERENCES teachers (id),
  title varchar(160) NOT NULL,
  credits int NOT NULL
);

CREATE TABLE students (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  email varchar(255) NOT NULL,
  enrolled_on date NOT NULL
);

CREATE TABLE enrollments (
  student_id bigint NOT NULL REFERENCES students (id),
  course_id bigint NOT NULL REFERENCES courses (id),
  grade varchar(2),
  enrolled_on date NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

Design notes

Enrollments is more than a plain junction table: it stores the grade and the date, which belong to the pairing rather than to either side.
To allow co-teaching, replace courses.teacher_id with a second junction table between teachers and courses.
grade stays empty until the course is finished.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.