LMS Education Schema — Lab

Diagram Views

erDiagram users ||--o{ courses : teaches courses ||--o{ course_lessons : organizes lessons ||--o{ course_lessons : reused_in users ||--o{ enrollments : joins courses ||--o{ enrollments : has enrollments ||--o{ lesson_progress : tracks lessons ||--o{ lesson_progress : measured_by users { bigint id PK text email text role } courses { bigint id PK bigint instructor_id FK text slug text title } lessons { bigint id PK text slug int duration_minutes } course_lessons { bigint course_id PK,FK bigint lesson_id PK,FK int position } enrollments { bigint id PK bigint course_id FK bigint student_id FK text status } lesson_progress { bigint enrollment_id PK,FK bigint lesson_id PK,FK int progress_percent }

SQL Snippets

schema.sql

create table users (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  role text not null check (role in ('student','instructor','admin')),
  created_at timestamptz not null default now()
);

create table courses (
  id bigint primary key generated always as identity,
  instructor_id bigint not null references users(id),
  slug text not null unique,
  title text not null,
  level text not null check (level in ('beginner','intermediate','advanced')),
  is_published boolean not null default false,
  created_at timestamptz not null default now()
);

create table lessons (
  id bigint primary key generated always as identity,
  slug text not null unique,
  title text not null,
  duration_minutes integer not null check (duration_minutes > 0)
);

create table course_lessons (
  course_id bigint not null references courses(id) on delete cascade,
  lesson_id bigint not null references lessons(id),
  position integer not null check (position > 0),
  primary key (course_id, lesson_id),
  unique (course_id, position)
);

create table enrollments (
  id bigint primary key generated always as identity,
  course_id bigint not null references courses(id) on delete cascade,
  student_id bigint not null references users(id) on delete cascade,
  status text not null check (status in ('active','completed','cancelled')),
  enrolled_at timestamptz not null default now(),
  unique (course_id, student_id)
);

create table lesson_progress (
  enrollment_id bigint not null references enrollments(id) on delete cascade,
  lesson_id bigint not null references lessons(id),
  completed_at timestamptz,
  progress_percent integer not null default 0 check (progress_percent between 0 and 100),
  primary key (enrollment_id, lesson_id)
);

create index idx_courses_instructor on courses(instructor_id);
create index idx_enrollments_student_status on enrollments(student_id, status);

seed.sql

insert into users (email, full_name, role)
values
  ('teach@academy.dev', 'Instructor Kim', 'instructor'),
  ('student1@academy.dev', 'Student One', 'student'),
  ('student2@academy.dev', 'Student Two', 'student');

insert into courses (instructor_id, slug, title, level, is_published)
values
  (1, 'sql-for-builders', 'SQL for Builders', 'beginner', true);

insert into lessons (slug, title, duration_minutes)
values
  ('intro-relational', 'Intro to Relational Modeling', 18),
  ('joins-ctes', 'Joins and CTE Basics', 24),
  ('indexing-101', 'Indexing Fundamentals', 21);

insert into course_lessons (course_id, lesson_id, position)
values
  (1, 1, 1),
  (1, 2, 2),
  (1, 3, 3);

insert into enrollments (course_id, student_id, status)
values
  (1, 2, 'active'),
  (1, 3, 'completed');

insert into lesson_progress (enrollment_id, lesson_id, completed_at, progress_percent)
values
  (1, 1, now() - interval '1 day', 100),
  (1, 2, null, 45),
  (2, 1, now() - interval '7 days', 100),
  (2, 2, now() - interval '6 days', 100),
  (2, 3, now() - interval '5 days', 100);

queries.sql

-- 1) Enrollment count per course
select
  c.slug,
  c.title,
  count(e.id) as enrolled_students
from courses c
left join enrollments e on e.course_id = c.id and e.status <> 'cancelled'
group by c.id
order by enrolled_students desc;

-- 2) Student completion rate in a course
select
  e.id as enrollment_id,
  u.email as student_email,
  round(avg(lp.progress_percent)::numeric, 2) as avg_progress
from enrollments e
join users u on u.id = e.student_id
left join lesson_progress lp on lp.enrollment_id = e.id
where e.course_id = 1
group by e.id, u.email
order by avg_progress desc;

-- 3) Lessons with lowest completion
select
  l.slug,
  l.title,
  round(avg(lp.progress_percent)::numeric, 2) as avg_progress
from lesson_progress lp
join lessons l on l.id = lp.lesson_id
group by l.id
order by avg_progress asc;