Learning Management System Database Schema database schema

What this schema models

A complete learning management system database design covering everything an online course platform actually needs. 34 tables and 13 enums spanning users and scoped roles, a course catalog with modules and lessons, cohorts, enrolment, per-lesson progress tracking, assignments with rubric grading, quizzes with multi-attempt scoring, a weighted gradebook, live sessions with attendance, discussions, announcements and verifiable certificates. THE THREE RULES 1. Progress is tracked per LESSON per ENROLMENT, not per user per course. A learner who takes the same course twice in different cohorts gets two independent progress trails and neither overwrites the other. lesson_progress also stores last_position_seconds, which is what resumes a video where the learner left it. 2. Grades are SNAPSHOTS. gradebook_items freeze max_points and weight_percent per cohort at publish time, so re-weighting an assessment next term cannot silently rewrite last term's transcript. That single decision is the difference between a gradebook you can defend and one you cannot. 3. Attempts are ROWS. A quiz attempt or assignment resubmission is a new row with a higher attempt_number, never an UPDATE over the previous one. That is what makes "best of three" and "show me what they answered the first time" both answerable from the same table. DETAILS WORTH STEALING - The COURSE is the syllabus; the COHORT is a run of it. Dates, capacity and enrolment hang off the cohort, so the same course can run every term without cloning content. Self-paced offerings still get one cohort so every enrolment has a parent. - user_roles carries a nullable course_id. Null is a global role, set is scoped to one course. That one column is the whole per-course instructor model. - Roles are a TABLE, not an enum, so a deployment can add its own without a migration. - Rubric scores are stored per criterion. The assignment grade is their sum, which is what makes marking defensible when a student appeals. - quiz_attempts snapshots max_score, so editing a quiz later cannot distort an old percentage. - grades.published_at null means the instructor can see the mark and the learner cannot — release is a timestamp, not a separate workflow table. - Certificate revocation is a timestamp, never a delete, so an old verification URL still resolves and says revoked. - Discussions with cohort_id null span every run of a course; set, they scope to one cohort. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by zone — identity, catalog, enrolment, assignments, quizzes, gradebook, live sessions and communication — with sticky notes explaining each decision and every table carrying its own Note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building an LMS or course marketplace, students who need a realistic e-learning ER diagram for a DBMS project, and education teams evaluating a data model before committing to Moodle, Canvas or a custom build.

Tables (34)

Relationships (59)

• announcements.author_id references users.id many to one
• announcements.cohort_id references cohorts.id many to one
• assignment_submissions.assignment_id references assignments.id many to one
• assignment_submissions.enrollment_id references enrollments.id many to one
• assignments.course_id references courses.id many to one
• assignments.module_id references modules.id many to one
• certificates.enrollment_id references enrollments.id many to one
• cohort_members.cohort_id references cohorts.id many to one
• cohort_members.user_id references users.id many to one
• cohorts.course_id references courses.id many to one
• course_categories.parent_id references course_categories.id many to one
• course_instructors.course_id references courses.id many to one
• course_instructors.user_id references users.id many to one
• courses.category_id references course_categories.id many to one
• discussion_posts.author_id references users.id many to one
• discussion_posts.discussion_id references discussions.id many to one
• discussion_posts.parent_post_id references discussion_posts.id many to one
• discussions.cohort_id references cohorts.id many to one
• discussions.course_id references courses.id many to one
• discussions.created_by references users.id many to one
• discussions.lesson_id references lessons.id many to one
• enrollments.cohort_id references cohorts.id many to one
• enrollments.user_id references users.id many to one
• gradebook_items.assignment_id references assignments.id many to one
• gradebook_items.cohort_id references cohorts.id many to one
• gradebook_items.quiz_id references quizzes.id many to one
• grades.enrollment_id references enrollments.id many to one
• grades.gradebook_item_id references gradebook_items.id many to one
• grades.graded_by references users.id many to one
• lesson_progress.enrollment_id references enrollments.id many to one
• lesson_progress.lesson_id references lessons.id many to one
• lesson_resources.lesson_id references lessons.id many to one
• lessons.module_id references modules.id many to one
• live_sessions.cohort_id references cohorts.id many to one
• live_sessions.host_id references users.id many to one
• modules.course_id references courses.id many to one
• notifications.course_id references courses.id many to one
• notifications.user_id references users.id many to one
• question_options.question_id references quiz_questions.id many to one
• quiz_answers.attempt_id references quiz_attempts.id many to one
• quiz_answers.question_id references quiz_questions.id many to one
• quiz_answers.selected_option_id references question_options.id many to one
• quiz_attempts.enrollment_id references enrollments.id many to one
• quiz_attempts.quiz_id references quizzes.id many to one
• quiz_questions.quiz_id references quizzes.id many to one
• quizzes.course_id references courses.id many to one
• quizzes.module_id references modules.id many to one
• rubric_criteria.rubric_id references rubrics.id many to one
• rubric_scores.criterion_id references rubric_criteria.id many to one
• rubric_scores.scored_by references users.id many to one
• rubric_scores.submission_id references assignment_submissions.id many to one
• rubrics.assignment_id references assignments.id many to one
• session_attendance.enrollment_id references enrollments.id many to one
• session_attendance.session_id references live_sessions.id many to one
• submission_files.submission_id references assignment_submissions.id many to one
• user_roles.course_id references courses.id many to one
• user_roles.role_id references roles.id many to one
• user_roles.user_id references users.id many to one
• users.id references user_profiles.user_id one to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Learning Management System Database Schema schema?

34 tables and 59 relationships, across 252 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.