Learning Management System data model
Normalized (3NF) LMS model. Instructors author courses broken into modules and lessons; learners enroll and progress, submitting assignments that receive grades and taking quizzes that record attempts, culminating in certificates. Learner education records (grades, scores, feedback) are FERPA-classified and masked.
Learning management system operations: learners and instructors, the course/module/lesson content hierarchy, enrollments and progress, assessable work (assignments, submissions, and grades), quizzes and attempts, and issued certificates — modeled as a normalized OLTP learning platform schema with FERPA-protected education records classified and masked. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 13
- tables
- 113
- columns
- 15
- relationships
- 8
- 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
Course / module / lesson content hierarchy
Structuring content as course to module to lesson mirrors how LMS platforms (e.g. Canvas, Moodle) organize curriculum and keeps sequencing and reuse normalized.
Submission and grade separated from assignment
An assignment is the task definition while submissions are per-learner attempts and grades are the assessed outcomes; keeping them distinct supports resubmission, late penalties, and re-grading.
FERPA classification on education records
Grades, quiz scores, and instructor feedback are protected education records under FERPA, so they are classified confidential and masked by default.
Normalized (3NF) rather than dimensional
LMS activity is transactional — enrollments, submissions, and attempts are continuously written and updated — favoring relational integrity over analytical roll-ups.
Tables in the Learning Management System 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 (12)
learner
Grain: one row per learner/student
Student account; FERPA-protected education record.
| Column | Type | Keys |
|---|---|---|
| learner_key | bigint | PK |
| learner_number | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| date_of_birth | date | PII |
| phone | varchar | PII |
| country | varchar | |
| timezone | varchar | |
| status | varchar | |
| enrolled_date | date |
instructor
Grain: one row per instructor
Teaching staff.
| Column | Type | Keys |
|---|---|---|
| instructor_key | bigint | PK |
| instructor_number | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| title | varchar | |
| department | varchar | |
| bio | varchar | |
| hire_date | date | |
| is_active | boolean |
course
Grain: one row per course
Catalog course.
| Column | Type | Keys |
|---|---|---|
| course_key | bigint | PK |
| instructor_key | bigint | |
| course_code | varchar | BK |
| title | varchar | |
| description | varchar | |
| subject_area | varchar | |
| level | varchar | |
| credit_hours | decimal | |
| language | varchar | |
| is_published | boolean | |
| created_at | timestamp |
module
Grain: one row per module within a course
Course unit.
| Column | Type | Keys |
|---|---|---|
| module_key | bigint | PK |
| course_key | bigint | |
| module_code | varchar | BK |
| title | varchar | |
| sequence_number | integer | |
| description | varchar | |
| is_required | boolean |
lesson
Grain: one row per lesson within a module
Lesson content.
| Column | Type | Keys |
|---|---|---|
| lesson_key | bigint | PK |
| module_key | bigint | |
| lesson_code | varchar | BK |
| title | varchar | |
| sequence_number | integer | |
| content_type | varchar | |
| content_url | varchar | |
| duration_minutes | integer |
enrollment
Grain: one row per learner enrolled in a course
Learner-course registration.
| Column | Type | Keys |
|---|---|---|
| enrollment_key | bigint | PK |
| learner_key | bigint | |
| course_key | bigint | |
| enrollment_number | varchar | BK |
| enrolled_at | timestamp | |
| completion_percentage | decimal | |
| final_grade | varchar | |
| status | varchar | |
| completed_at | timestamp |
assignment
Grain: one row per assignment in a course
Graded task definition.
| Column | Type | Keys |
|---|---|---|
| assignment_key | bigint | PK |
| course_key | bigint | |
| module_key | bigint | |
| assignment_code | varchar | BK |
| title | varchar | |
| description | varchar | |
| max_points | decimal | |
| weight_percentage | decimal | |
| due_date | timestamp | |
| is_published | boolean |
submission
Grain: one row per learner submission to an assignment
Submitted work.
| Column | Type | Keys |
|---|---|---|
| submission_key | bigint | PK |
| assignment_key | bigint | |
| enrollment_key | bigint | |
| submission_number | varchar | BK |
| attempt_number | smallint | |
| content_url | varchar | |
| submitted_at | timestamp | |
| is_late | boolean | |
| status | varchar |
grade
Grain: one row per grade for a submission
Assessment outcome; FERPA-protected.
| Column | Type | Keys |
|---|---|---|
| grade_key | bigint | PK |
| submission_key | bigint | |
| instructor_key | bigint | |
| points_earned | decimal | |
| points_possible | decimal | |
| letter_grade | varchar | |
| feedback | varchar | |
| graded_at | timestamp |
quiz
Grain: one row per quiz
Assessment quiz.
| Column | Type | Keys |
|---|---|---|
| quiz_key | bigint | PK |
| lesson_key | bigint | |
| quiz_code | varchar | BK |
| title | varchar | |
| question_count | smallint | |
| max_points | decimal | |
| time_limit_minutes | integer | |
| max_attempts | smallint | |
| passing_score | decimal |
quiz_attempt
Grain: one row per learner attempt at a quiz
Quiz attempt; FERPA-protected.
| Column | Type | Keys |
|---|---|---|
| quiz_attempt_key | bigint | PK |
| quiz_key | bigint | |
| enrollment_key | bigint | |
| attempt_number | smallint | |
| score | decimal | |
| is_passed | boolean | |
| started_at | timestamp | |
| submitted_at | timestamp | |
| duration_seconds | integer |
certificate
Grain: one row per issued certificate
Completion credential.
| Column | Type | Keys |
|---|---|---|
| certificate_key | bigint | PK |
| enrollment_key | bigint | |
| certificate_number | varchar | BK |
| title | varchar | |
| issued_date | date | |
| expiry_date | date | |
| verification_code | varchar | |
| status | varchar |
Lookup tables (1)
content_type_ref
Grain: static reference of lesson content types
Content-type reference.
| Column | Type | Keys |
|---|---|---|
| content_type_key | bigint | PK |
| type_code | varchar | BK |
| type_name | varchar | |
| description | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- course.instructor_key → instructor (many-to-one)
- module.course_key → course (many-to-one)
- lesson.module_key → module (many-to-one)
- enrollment.learner_key → learner (many-to-one)
- enrollment.course_key → course (many-to-one)
- assignment.course_key → course (many-to-one)
- assignment.module_key → module (many-to-one)
- submission.assignment_key → assignment (many-to-one)
- submission.enrollment_key → enrollment (many-to-one)
- grade.submission_key → submission (many-to-one)
- grade.instructor_key → instructor (many-to-one)
- quiz.lesson_key → lesson (many-to-one)
- quiz_attempt.quiz_key → quiz (many-to-one)
- quiz_attempt.enrollment_key → enrollment (many-to-one)
- certificate.enrollment_key → enrollment (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.