K-12 School Information System (Ed-Fi inspired) data model
Third-normal-form mirror of the Ed-Fi K-12 entities as they would land from an Ed-Fi ODS or an SIS export. Lookups carry the descriptor codes (grade level, term, subject area, attendance, discipline action, enrollment status), normal tables carry the organizational and student-family records, and outcome tables (attendance, grade, assessment, discipline) reference student + section + term. Student and parent PII (names, DOB, SSN, address, contact info) are FERPA + COPPA restricted; the self-referencing course and school parents are modeled as plain bigint keys without a relationship.
Normalized replica of a K-12 student information system aligned to the Ed-Fi Unifying Data Model — Local Education Agency (district), School, Course, Section, Staff, Student, Parent/Guardian, plus the outcome events of Enrollment, Attendance, Grade, Assessment, and Discipline. Suitable as a warehouse staging layer for SIS exports (PowerSchool, Infinite Campus, Skyward) and Ed-Fi ODS replicas. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 21
- tables
- 186
- columns
- 33
- relationships
- 16
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Student-section association as the class-enrollment grain
Ed-Fi separates StudentSectionAssociation (a student's enrollment in a specific class) from StudentSchoolAssociation (enrollment in a school + grade level over time). Replicating both keeps schedule churn (drops, adds, swaps) distinct from school-level enrollment history.
Separate student_enrollment entity for school/grade history
A student's school and grade level change across years and mid-year transfers; modeling student_enrollment as its own entity preserves the begin/end-dated history that Ed-Fi StudentSchoolAssociation captures.
Parent ↔ student many-to-many via a link table
Ed-Fi models the parent-student relationship as StudentParentAssociation because one student can have multiple guardians (and one guardian multiple children); a link table avoids duplicating parent rows per child and carries the relationship_type and custody_status.
FERPA / COPPA restricted PII on student identifiers and addresses
FERPA restricts disclosure of personally identifiable information from education records; SSN, date of birth, and home address are tagged restricted, and student/parent names + email are marked PII so the governance report surfaces them up-front.
Course prerequisite chain modeled as a plain self-referencing key
Course prerequisites form a directed acyclic graph (Algebra II requires Algebra I, etc.); keeping parent_course_key as a plain bigint without a declared relationship avoids cyclic FK dependencies during DDL topo-sort while still letting the catalog walk the chain in SQL.
Tables in the K-12 School Information System (Ed-Fi inspired) model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (14)
local_education_agency
Grain: one row per LEA / district
Ed-Fi LocalEducationAgency — district-level organization.
| Column | Type | Keys |
|---|---|---|
| local_education_agency_key | bigint | PK |
| lea_id | varchar | BK |
| district_name | varchar | |
| state | varchar | |
| district_type | varchar | |
| enrollment_size_band | varchar | |
| superintendent_name | varchar | PII |
school
Grain: one row per Ed-Fi School
Ed-Fi School — campus-level organization.
| Column | Type | Keys |
|---|---|---|
| school_key | bigint | PK |
| local_education_agency_key | bigint | |
| school_id | varchar | BK |
| school_name | varchar | |
| school_type | varchar | |
| low_grade_level | varchar | |
| high_grade_level | varchar | |
| principal_name | varchar | PII |
| parent_school_key | bigint | |
| is_charter | boolean | |
| is_title_one | boolean |
staff
Grain: one row per staff member
Ed-Fi Staff — teachers, principals, counselors, aides, administrators.
| Column | Type | Keys |
|---|---|---|
| staff_key | bigint | PK |
| school_key | bigint | |
| staff_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| role | varchar | |
| subject_area_primary | varchar | |
| hire_date | date | |
| employment_status | varchar | |
| certification | varchar | |
| varchar | PII |
course
Grain: one row per course offering in the catalog
Ed-Fi Course — district course catalog entry.
| Column | Type | Keys |
|---|---|---|
| course_key | bigint | PK |
| subject_area_key | bigint | |
| course_code | varchar | BK |
| course_title | varchar | |
| credit_hours | decimal | |
| course_level | varchar | |
| grade_level_min | varchar | |
| grade_level_max | varchar | |
| parent_course_key | bigint |
section
Grain: one row per section (a class period of a course)
Ed-Fi Section — a specific scheduled offering of a course.
| Column | Type | Keys |
|---|---|---|
| section_key | bigint | PK |
| course_key | bigint | |
| school_key | bigint | |
| school_year_key | bigint | |
| term_key | bigint | |
| staff_key | bigint | |
| section_id | varchar | BK |
| section_number | varchar | |
| classroom_id | varchar | |
| period | varchar | |
| max_students | integer | |
| meeting_pattern | varchar | |
| is_active | boolean |
student
Grain: one row per student
Ed-Fi Student — the central learner entity.
| Column | Type | Keys |
|---|---|---|
| student_key | bigint | PK |
| student_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| gender | varchar | |
| ethnicity | varchar | |
| race | varchar | |
| primary_language | varchar | |
| ell_status | varchar | |
| iep_504_status | varchar | |
| free_reduced_lunch_status | varchar | |
| ssn_masked | varchar | PII |
| address | varchar | PII |
| varchar | PII | |
| parent_student_id | varchar | |
| enrollment_date | date | |
| is_active | boolean |
parent_guardian
Grain: one row per parent or guardian
Ed-Fi Parent — parent/guardian contact record.
| Column | Type | Keys |
|---|---|---|
| parent_guardian_key | bigint | PK |
| parent_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| relationship | varchar | |
| varchar | PII | |
| phone | varchar | PII |
| is_primary_contact | boolean | |
| lives_with_student | boolean |
student_parent_link
Grain: one row per student↔parent association
Ed-Fi StudentParentAssociation — M:N student-to-parent linker.
| Column | Type | Keys |
|---|---|---|
| student_parent_link_key | bigint | PK |
| student_key | bigint | |
| parent_guardian_key | bigint | |
| link_id | varchar | BK |
| relationship_type | varchar | |
| custody_status | varchar |
student_enrollment
Grain: one row per student enrollment in a school/grade/year
Ed-Fi StudentSchoolAssociation — school + grade-level enrollment over time.
| Column | Type | Keys |
|---|---|---|
| student_enrollment_key | bigint | PK |
| student_key | bigint | |
| school_key | bigint | |
| school_year_key | bigint | |
| grade_level_key | bigint | |
| enrollment_status_key | bigint | |
| enrollment_id | varchar | BK |
| entry_date | date | |
| exit_date | date | |
| entry_grade_level | varchar | |
| exit_reason | varchar |
student_section_association
Grain: one row per student↔section enrollment
Ed-Fi StudentSectionAssociation — enrollment in a specific class.
| Column | Type | Keys |
|---|---|---|
| student_section_association_key | bigint | PK |
| student_key | bigint | |
| section_key | bigint | |
| term_key | bigint | |
| association_id | varchar | BK |
| begin_date | date | |
| end_date | date | |
| enrollment_status | varchar |
attendance_event
Grain: one row per attendance event per student/section/day
Ed-Fi StudentSectionAttendanceEvent.
| Column | Type | Keys |
|---|---|---|
| attendance_event_key | bigint | PK |
| student_key | bigint | |
| section_key | bigint | |
| attendance_code_key | bigint | |
| attendance_event_id | varchar | BK |
| attendance_date | date | |
| period | varchar | |
| was_tardy_minutes | integer | |
| recorded_by | varchar | PII |
grade
Grain: one row per student/section/term grade
Ed-Fi Grade — reported grade for a student in a section.
| Column | Type | Keys |
|---|---|---|
| grade_key | bigint | PK |
| student_key | bigint | |
| section_key | bigint | |
| term_key | bigint | |
| grade_level_key | bigint | |
| grade_id | varchar | BK |
| grading_period | varchar | |
| letter_grade | varchar | |
| numeric_grade | decimal | |
| grade_points | decimal | |
| is_final | boolean | |
| is_passing | boolean |
assessment_result
Grain: one row per student assessment result
Ed-Fi StudentAssessment — standardized assessment results.
| Column | Type | Keys |
|---|---|---|
| assessment_result_key | bigint | PK |
| student_key | bigint | |
| school_year_key | bigint | |
| subject_area_key | bigint | |
| assessment_result_id | varchar | BK |
| assessment_name | varchar | |
| administered_date | date | |
| scale_score | decimal | |
| performance_level | varchar | |
| percentile_rank | decimal | |
| accommodations_used | varchar |
discipline_incident
Grain: one row per discipline incident
Ed-Fi DisciplineIncident — discipline event with assigned action.
| Column | Type | Keys |
|---|---|---|
| discipline_incident_key | bigint | PK |
| student_key | bigint | |
| school_key | bigint | |
| school_year_key | bigint | |
| discipline_action_key | bigint | |
| staff_key | bigint | |
| incident_id | varchar | BK |
| incident_date | date | |
| behavior_code | varchar | |
| days_assigned | integer | |
| location_in_school | varchar | |
| reporter_role | varchar |
Lookup tables (7)
school_year
Grain: one row per school year
Ed-Fi SchoolYearType — academic year reference.
| Column | Type | Keys |
|---|---|---|
| school_year_key | bigint | PK |
| school_year_id | varchar | BK |
| year_label | varchar | |
| start_date | date | |
| end_date | date | |
| is_current | boolean |
grade_level
Grain: one row per grade level
Ed-Fi GradeLevelDescriptor.
| Column | Type | Keys |
|---|---|---|
| grade_level_key | bigint | PK |
| grade_level_id | varchar | BK |
| grade_label | varchar | |
| age_typical | integer | |
| is_elementary | boolean | |
| is_middle | boolean | |
| is_high | boolean |
term
Grain: one row per academic term within a school year
Ed-Fi TermDescriptor.
| Column | Type | Keys |
|---|---|---|
| term_key | bigint | PK |
| term_id | varchar | BK |
| term_name | varchar | |
| start_date | date | |
| end_date | date | |
| school_year_id | varchar | |
| is_quarter | boolean | |
| is_semester | boolean |
subject_area
Grain: one row per academic subject area
Ed-Fi AcademicSubjectDescriptor.
| Column | Type | Keys |
|---|---|---|
| subject_area_key | bigint | PK |
| subject_area_id | varchar | BK |
| subject_name | varchar | |
| subject_category | varchar |
attendance_code
Grain: one row per attendance event code
Ed-Fi AttendanceEventCategoryDescriptor.
| Column | Type | Keys |
|---|---|---|
| attendance_code_key | bigint | PK |
| attendance_code_id | varchar | BK |
| description | varchar | |
| counts_as_present | boolean | |
| counts_as_absent | boolean |
discipline_action
Grain: one row per discipline action type
Ed-Fi DisciplineActionDescriptor.
| Column | Type | Keys |
|---|---|---|
| discipline_action_key | bigint | PK |
| discipline_action_id | varchar | BK |
| description | varchar | |
| severity_level | integer | |
| days_typical | integer |
enrollment_status
Grain: one row per enrollment status code
Ed-Fi StudentEnrollmentStatusDescriptor.
| Column | Type | Keys |
|---|---|---|
| enrollment_status_key | bigint | PK |
| status_id | varchar | BK |
| description | varchar | |
| is_active_enrollment | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- school.local_education_agency_key → local_education_agency (many-to-one)
- staff.school_key → school (many-to-one)
- course.subject_area_key → subject_area (many-to-one)
- section.course_key → course (many-to-one)
- section.school_key → school (many-to-one)
- section.school_year_key → school_year (many-to-one)
- section.term_key → term (many-to-one)
- section.staff_key → staff (many-to-one)
- student_parent_link.student_key → student (many-to-one)
- student_parent_link.parent_guardian_key → parent_guardian (many-to-one)
- student_enrollment.student_key → student (many-to-one)
- student_enrollment.school_key → school (many-to-one)
- student_enrollment.school_year_key → school_year (many-to-one)
- student_enrollment.grade_level_key → grade_level (many-to-one)
- student_enrollment.enrollment_status_key → enrollment_status (many-to-one)
- student_section_association.student_key → student (many-to-one)
- student_section_association.section_key → section (many-to-one)
- student_section_association.term_key → term (many-to-one)
- attendance_event.student_key → student (many-to-one)
- attendance_event.section_key → section (many-to-one)
- attendance_event.attendance_code_key → attendance_code (many-to-one)
- grade.student_key → student (many-to-one)
- grade.section_key → section (many-to-one)
- grade.term_key → term (many-to-one)
- grade.grade_level_key → grade_level (many-to-one)
- assessment_result.student_key → student (many-to-one)
- assessment_result.school_year_key → school_year (many-to-one)
- assessment_result.subject_area_key → subject_area (many-to-one)
- discipline_incident.student_key → student (many-to-one)
- discipline_incident.school_key → school (many-to-one)
- discipline_incident.school_year_key → school_year (many-to-one)
- discipline_incident.discipline_action_key → discipline_action (many-to-one)
- discipline_incident.staff_key → staff (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.