The data model of a school (and why yours is probably wrong)

Almost every K-12 integration disaster traces back to a schema decision made in week two, by someone reasonable, who assumed a school was a slightly unusual SaaS tenant.

It isn't. A school is a time-bounded, multi-tenant, many-to-many structure where the authoritative copy of nearly every fact lives in somebody else's system.

Here's what that structure actually looks like, and which parts are expensive to get wrong.

Start from the entities, not from your product

The vocabulary below is OneRoster's, because that's what you'll be handed. It maps closely onto how districts think, which isn't an accident.

Org. A district is an org and a school is an org, in the same table, related by a parent id. This trips people up. They build District and School as separate types, then meet a district with an intermediate level (a region, a campus group, a network) and have nowhere to put it. Model orgs as a tree with a type.

Academic session. The school year, and inside it terms, semesters and grading periods. This is nested, with real dates, and it's the entity teams most often flatten into a year string. Don't. Grading periods decide which scores count, and two schools in the same district often run different ones. A high school on semesters and an elementary school on trimesters, same superintendent.

Course. The catalogue entry. "Algebra I."

Class. The section a student is actually in. "Algebra I, period 3, Ms Whitfield." This is where the humans are. A class belongs to a course and to one or more terms, and carries a type that's usually scheduled or homeroom. Homerooms are in your data and aren't instructional. A report that treats them as classes shows every elementary student enrolled in a course called "Homeroom 4B".

User. A person. Student, teacher, administrator, parent, guardian, aide, counsellor. One person can hold several of these. A teacher who is also a parent in the same district is common, and is one human with one identity and two very different permission sets.

Enrolment. The join between a user and a class, with a role and a date range. Treat it as a first-class entity with a lifetime, not a foreign key on a user. Nearly every hard question in this domain is a question about enrolments over time.

The five decisions that are expensive to reverse

1. Grade level is a string

PK, KG, 01 through 12, UG for ungraded, and several others depending on the vocabulary a district uses.

Parse it as an integer and kindergarten silently disappears from every report, pre-K becomes null, and an ungraded special education setting becomes a crash.

Store it as a string, keep a lookup for ordering, never sort it numerically.

2. An enrolment has a beginning and an end

A student joins a class in March. Another leaves in November. A teacher takes over a section mid-year when a colleague goes on leave. A course is co-taught by two teachers, one flagged primary, with a push-in specialist who appears in the roster and is neither.

If your model is class.students: User[], none of that is expressible. You'll either overwrite history, so a report run in June can't reconstruct who was in the room in March, or you'll accumulate ghosts, so a class shows 41 students and 12 of them left.

The right shape is an enrolment row per user, per class, per role, with a begin date, an end date and a status. It costs a join. It buys you every historical question anyone will ever ask.

3. One human has many identifiers

The same student reaches you as a Clever id in a nightly sync, an LTI sub claim from inside Canvas, a Google subject on a Chromebook, and a sourcedId in a OneRoster feed. Four strings, one child.

Model it explicitly: an internal user id that never leaves your system, and an identities table of (provider, external_id, user_id). Resolve on the way in.

The failure mode isn't subtle. A student does their work under one identity on Monday and can't find it on Tuesday, and the teacher reports it as data loss. From where they're standing, it is.

Plan for the identifier to move. When a district migrates SIS, and they do, the sourcedId for every person can change. If that's your primary key, migration day orphans every piece of student work in the district. If it's one row in an identities table, migration day is a mapping exercise.

4. Email is not a key, and may not exist

Many districts don't issue email addresses to elementary students. Younger children sign in with usernames, class codes or QR badges. Email as a primary key, a uniqueness constraint or a password reset path designs those grades out of your product.

Names deserve care too. Students change names. Districts have policies about chosen and legal names that differ by state and sometimes by school. A product that surfaces a legal name where a chosen name was expected creates a real problem for a real child. Store both, know which one you're displaying, make it configurable.

5. You will never have all the data

Two independent reasons.

Scope. A district evaluating you will share twelve of its forty schools, and expects everything to be correct on that subset, and to stay correct when the other twenty-eight arrive in November without warning.

Minimisation. OneRoster has a demographics endpoint carrying race, ethnicity and birthdate. Most districts won't share it, and they're right not to. If a feature requires demographics, that feature doesn't work for most of the market.

Design as though you have names, roles, enrolments and nothing else. Treat anything more as an enhancement a district can opt into.

Deletion means something specific

OneRoster doesn't hard-delete. A record going away is marked status: tobedeleted, and interpreting that correctly matters.

A section marked tobedeleted in June means the school year ended. It does not mean destroy the evidence of what happened in it. Districts have lost a year of student work to a vendor that treated a soft-delete flag as an instruction, three weeks before a board review, and the vendor was technically following the feed.

The rule: soft deletes change visibility and enrolment, never retention. Retention is governed by your contract and by state law, and the two are related only by coincidence.

A schema that survives contact

orgs            (id, type, parent_id, source_ids…)
academic_terms  (id, org_id, type, parent_id, start_date, end_date)
courses         (id, org_id, title, subject, grade_levels[])
classes         (id, course_id, org_id, class_type, term_ids[])
users           (id, given_name, family_name, preferred_name, grade_level)
identities      (provider, external_id, user_id)
enrolments      (id, user_id, class_id, role, primary,
                 begin_date, end_date, status)
sync_records    (source, resource_type, external_id,
                 last_seen_at, last_hash, status)

Two of those aren't in any specification, and they're the ones that make the rest work. identities lets one human arrive through four doors. sync_records lets you diff a feed instead of replacing the world every night, which is the subject of a roster sync that survives the school year.

Seven questions to test it against

Every one of these is something a district will actually ask.

1. Who was in this class on 12 March?
2. This student moved from Lincoln Middle to Roosevelt Middle in January. Where is their work?
3. This section has two teachers. Which one is the teacher of record?
4. The district migrated SIS over the summer. Is this the same student?
5. Show me one student's complete record so I can hand it to their parent.
6. This school runs trimesters and that one runs semesters. Which grading period is this score in?
7. Delete everything for this district, and prove it.

If your schema answers all seven with a query, it will hold. If any of them needs a migration, fix it now. It's a week of work today and a quarter of work after you have production data in twelve districts.