Design a normalized database schema

1. Write requirements as simple sentences: “a customer can have several addresses”.
2. Add the queries you will run most often; they drive the indexes.
3. Paste the Mermaid diagram into a compatible viewer to review it.
4. Run the DDL on a test database before writing the final migrations.

Application: language school management · Requirements: students, courses, class groups, enrollments and monthly payments · Engine: PostgreSQL 16.

4. DDL (excerpt, PostgreSQL 16)

CREATE TABLE students (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz                -- soft delete
);

CREATE TABLE enrollments (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id      bigint NOT NULL REFERENCES students (id) ON DELETE RESTRICT,
  class_group_id  bigint NOT NULL REFERENCES class_groups (id) ON DELETE RESTRICT,
  enrolled_on     date NOT NULL DEFAULT current_date,
  UNIQUE (student_id, class_group_id)
);

CREATE TABLE monthly_payments (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  enrollment_id  bigint NOT NULL REFERENCES enrollments (id) ON DELETE RESTRICT,
  period         date NOT NULL CHECK (extract(day FROM period) = 1),
  amount_cents   integer NOT NULL CHECK (amount_cents >= 0),
  paid_at        timestamptz,
  UNIQUE (enrollment_id, period)
);

-- Partial index for "students with overdue payments"
CREATE INDEX idx_payments_unpaid ON monthly_payments (period) WHERE paid_at IS NULL;

ON DELETE RESTRICT prevents losing payment history if someone deletes a student, which is why soft deletes are used. Amounts are stored in cents to avoid rounding errors.

Ambiguities

• Does one payment cover a single enrollment or all of a student's enrollments that month? [FILL IN]…