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, and attendance hangs off enrollments, one row per student per class day.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • students / teachers — Separate tables: they share a name but nothing else — different lifecycles, different data.
  • courses — The catalog entry; CS-101 exists once regardless of how many times it is taught.
  • sections — The offering: a course, in a term, by a teacher. The thing students actually join.
  • enrollments — Student-to-section junction whose payload is the grade.
  • attendance — Hangs off the enrollment with a composite FK — you cannot be marked present in a class you are not enrolled in.

Design decisions worth copying

  • The course/section split is the heart of the 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. Copy freely — example schemas are meant to be taken.

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?

A guardians table plus a student_guardians junction — 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