ErdDocs

Example schemas / School Management System

School Management System ER diagram

A school management system database connects students to courses through enrollments, the junction that also stores the grade. Teachers teach sections of courses in specific terms. Attendance hangs off enrollments, one row per student per class day.

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

Hover a table to light up its relations, and click to pin them.

What each table does

  • students / teachersSeparate tables. They share a name but nothing else: different lifecycles, different data.
  • coursesThe catalog entry; CS-101 exists once regardless of how many times it is taught.
  • sectionsThe offering: a course, in a term, by a teacher. The thing students actually join.
  • enrollmentsStudent-to-section junction whose payload is the grade.
  • attendanceHangs off the enrollment with a composite FK, so you cannot be marked present in a class you are not enrolled in.

Design decisions worth copying

  • The course/section split is the main idea of this schema. Enrolling students in "courses" collapses the moment a course runs twice: two terms, two teachers, one grade column fighting over meaning.
  • attendance references enrollments through a composite foreign key (student_id, section_id). The database itself rejects attendance for non-enrolled students.
  • grade is nullable rather than defaulted: an absent grade is information (term in progress), not a zero.

The SQL

PostgreSQL dialect. Paste it into the tool above (it is already there), into your own database, or into a migration. You are free to copy it.

School Management System ER diagram: 6 tables and 5 relations, drawn by ErdDocs from the SQL below.School Management System ER diagram: 6 tables and 5 relations, drawn by ErdDocs from the SQL below.
The diagram ErdDocs generates from this SQL.
CREATE TABLE students (
    id          bigserial    PRIMARY KEY,
    student_no  varchar(20)  NOT NULL UNIQUE,
    full_name   varchar(120) NOT NULL,
    enrolled_on date         NOT NULL
);

CREATE TABLE teachers (
    id         bigserial    PRIMARY KEY,
    full_name  varchar(120) NOT NULL,
    department varchar(80)  NOT NULL
);

CREATE TABLE courses (
    id      bigserial    PRIMARY KEY,
    code    varchar(12)  NOT NULL UNIQUE,
    title   varchar(160) NOT NULL,
    credits int          NOT NULL
);
COMMENT ON COLUMN courses.code IS 'Catalog code, e.g. CS-101';

CREATE TABLE sections (
    id          bigserial   PRIMARY KEY,
    course_id   bigint      NOT NULL REFERENCES courses(id),
    teacher_id  bigint      NOT NULL REFERENCES teachers(id),
    term        varchar(20) NOT NULL,
    room        varchar(20)
);
COMMENT ON TABLE sections IS 'A course offered in a term by a teacher; students enroll in sections, not courses';

CREATE TABLE enrollments (
    student_id  bigint     NOT NULL REFERENCES students(id),
    section_id  bigint     NOT NULL REFERENCES sections(id),
    grade       varchar(2),
    PRIMARY KEY (student_id, section_id)
);
COMMENT ON COLUMN enrollments.grade IS 'NULL until the term ends';

CREATE TABLE attendance (
    student_id  bigint  NOT NULL,
    section_id  bigint  NOT NULL,
    class_date  date    NOT NULL,
    present     boolean NOT NULL,
    PRIMARY KEY (student_id, section_id, class_date),
    FOREIGN KEY (student_id, section_id) REFERENCES enrollments(student_id, section_id)
);

FAQ

Where do parents or guardians fit?

In a guardians table plus a student_guardians junction. It is many-to-many, since siblings share guardians and students can have several. The pattern is identical to post_tags in a blog schema.

Why not put teacher_id on courses?

Because the same course is taught by different teachers in different terms. The teacher belongs to the offering (section), not to the catalog entry.

More example schemas