From a paragraph to a schema: a tuition centre

The paragraph

We run a tuition centre. Students enrol in batches; a batch is a course (say, Class 10 Maths) running over a term with a fixed weekly timetable and a fee. A student can be in several batches and a batch has many students. Fees are paid in instalments, and we need to know who owes what. We take attendance at every session. Teachers are assigned to batches, and a teacher can teach several.

That is the whole brief, and it is enough. Designing the schema is the act of reading it slowly.

Nouns become tables, one grain each

Student, batch, course, teacher, session, enrolment, payment, attendance record. Say the grain of each: one row is one student; one row is one batch (a course in a term); one row is one session of a batch on a date; one row is one instalment paid. “Enrolment” is not in the paragraph as a noun, but “a student can be in several batches and a batch has many students” is a many-to-many relationship, and the earlier lesson said those need a table in the middle. It arrives with facts of its own: the date the student joined, and the fee agreed for that student, which may differ from the batch’s list fee.

The tables

CREATE TABLE students (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  phone       text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT students_phone_unique UNIQUE (phone)
);

CREATE TABLE teachers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL
);

CREATE TABLE batches (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  course_name text NOT NULL,                 -- 'Class 10 Maths'
  term_start  date NOT NULL,
  term_end    date NOT NULL,
  list_fee    numeric(10,2) NOT NULL CHECK (list_fee >= 0),
  teacher_id  bigint NOT NULL REFERENCES teachers (id),
  CONSTRAINT batches_term_valid CHECK (term_end > term_start)
);

CREATE TABLE enrolments (
  student_id  bigint NOT NULL REFERENCES students (id),
  batch_id    bigint NOT NULL REFERENCES batches (id),
  joined_on   date NOT NULL,
  agreed_fee  numeric(10,2) NOT NULL CHECK (agreed_fee >= 0),
  PRIMARY KEY (student_id, batch_id)
);

CREATE TABLE sessions (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  batch_id    bigint NOT NULL REFERENCES batches (id),
  held_on     date NOT NULL,
  CONSTRAINT sessions_one_per_day UNIQUE (batch_id, held_on)
);

CREATE TABLE attendance (
  session_id  bigint NOT NULL REFERENCES sessions (id),
  student_id  bigint NOT NULL REFERENCES students (id),
  present     boolean NOT NULL,
  PRIMARY KEY (session_id, student_id)
);

CREATE TABLE payments (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id  bigint NOT NULL REFERENCES students (id),
  batch_id    bigint NOT NULL REFERENCES batches (id),
  amount      numeric(10,2) NOT NULL CHECK (amount > 0),
  paid_on     date NOT NULL,
  reference   text NOT NULL UNIQUE,
  FOREIGN KEY (student_id, batch_id) REFERENCES enrolments (student_id, batch_id)
);

CREATE INDEX ON enrolments (batch_id);
CREATE INDEX ON sessions (batch_id, held_on);
CREATE INDEX ON payments (student_id, batch_id);

The decisions inside it

agreed_fee is copied onto the enrolment rather than read from the batch, for the reason the lesson on keeping facts in one place gave: it is the fee that was true for this student at this moment, and a later change to list_fee must not rewrite it.

payments references the enrolment through a two-column foreign key, so a payment can only be recorded against a batch the student is actually in. That is a rule the paragraph implied and nobody stated; the schema states it.

sessions_one_per_day says a batch meets at most once per day, which is what “a fixed weekly timetable” implies. If evening and morning sessions of the same batch ever exist, the constraint is the thing that will tell you the model has changed.

There is no balance_due column on students or enrolments. It is tempting, it is what the paragraph asks for, and it is derived: agreed fee minus payments. Stored, it goes stale the first time a payment is recorded without the update, and the concurrent-writes lesson showed how that happens. Computed, it is always right:

SELECT s.full_name, b.course_name,
       e.agreed_fee - coalesce(sum(p.amount), 0) AS balance_due
FROM enrolments e
JOIN students s ON s.id = e.student_id
JOIN batches  b ON b.id = e.batch_id
LEFT JOIN payments p ON p.student_id = e.student_id AND p.batch_id = e.batch_id
GROUP BY s.full_name, b.course_name, e.agreed_fee
HAVING e.agreed_fee - coalesce(sum(p.amount), 0) > 0;

That query is the “who owes what” report, and it is the test of the schema: it needs no tricks, no DISTINCT, no fan-trap defence, because there is exactly one child table in it. Attendance percentage per batch is a second such query, and the earlier lessons on ratios and on spines say how to write it so that a student who joined late is not counted absent for sessions before they joined.

What was left out on purpose

No deleted_at yet, because nothing in the brief says students are removed; when it does, the history lesson says how. No JSON, because every attribute here is known and typed. No teacher-to-batch bridge table, because the paragraph says a batch has a teacher, singular; if two teachers ever share a batch, the change is a new table and a moved column, and the foreign key will make the change visible rather than silent. A schema is a set of claims about the world, and leaving a claim out is also a claim.

The habit

Read the requirements twice. List the nouns, give each a grain. Find the many-to-many sentences and give each its table. For every number that could be derived, do not store it. For every rule the paragraph implies, write a constraint. Then write the two or three reports the schema exists to produce, and if any needs a workaround, the schema is not finished.