Cohorts are not just a group id

The obvious model puts cohort_id on the student. It is one column, every query joins cleanly, and it is wrong the first time anyone changes class mid-year.

Membership has a period

When a student transfers in November, updating the column rewrites history. Their September grades now appear under the new class. The old teacher’s mark distribution changes retroactively. Attendance reports for October show a student who was not there.

Membership is an interval, not an attribute:

cohort_memberships
  student_id
  cohort_id
  joined_on
  left_on          -- null while current
  reason           -- enrolment | transfer | withdrawal

“Which class is this student in” becomes a query with a date. So does “who was in 9B in October”, and that one is unanswerable in the column model.

Records carry their own context

Every graded item, attendance mark, and behaviour note stores the cohort it was recorded against, at the moment it was recorded. Not derived by joining to current membership — copied onto the row.

That is deliberate denormalisation and it is correct here. The alternative is that a transfer silently rewrites every historical report, which is the exact failure the interval table was meant to prevent.

Overlaps are real

A student can be in a tutor group and three subject sets simultaneously. Cohorts need a kind:

cohort_kind   tutor_group | teaching_set | intervention | year_group

Uniqueness is per kind — one tutor group at a time, several teaching sets. Enforce that in the database with a partial exclusion constraint rather than in application code, because bulk imports at the start of term will not go through your application code.

Rollover is the annual disaster

Every July, whole-school promotion moves everyone up a year. Done as an update, it destroys the previous year in one statement. Done as closing memberships with left_on and opening new ones, last year’s reports still run — which matters, because they are what the inspection asks for.