LMS

Tables (15)

attempts

  • attempt_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • quiz_id — BIGINT — Foreign key → quizzes.quiz_id
  • exam_id — BIGINT — Foreign key → exams.exam_id
  • score — INT — NOT NULL
  • is_passed — TINYINT(1) — NOT NULL
  • attempted_at — DATETIME — NOT NULL
  • INDEX — idx_att_user
  • INDEX — idx_att_quiz
  • INDEX — idx_att_exam

certificates

  • certificate_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • round_id — BIGINT — NOT NULL
  • issued_at — DATETIME — NOT NULL
  • file_url — VARCHAR(500)

choices

  • choice_id — BIGINT — Primary key — NOT NULL
  • question_id — BIGINT — Foreign key → questions.question_id — NOT NULL
  • choice_text — VARCHAR(300) — NOT NULL
  • is_correct — TINYINT(1) — NOT NULL
  • sort_order — INT — NOT NULL
  • INDEX — idx_choice_question

course_rounds

  • round_id — BIGINT — Primary key — NOT NULL
  • course_id — BIGINT — Foreign key → courses.course_id — NOT NULL
  • round_no — INT — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_round_course

course_videos

  • video_id — BIGINT — Primary key — NOT NULL
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • video_url — VARCHAR(500) — NOT NULL
  • duration_sec — INT — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_videos_lecture

courses

  • course_id — BIGINT — Primary key — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • category — VARCHAR(100)
  • target_role — TINYINT — NOT NULL
  • duration_min — INT
  • thumbnail_url — VARCHAR(500)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — idx_courses_category

departments

  • department_id — INT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • created_at — DATETIME — NOT NULL

enrollments

  • enrollment_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • round_id — BIGINT — Foreign key → course_rounds.round_id — NOT NULL
  • approval_status — VARCHAR(20) — NOT NULL
  • progress — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • enrolled_at — DATETIME — NOT NULL
  • approved_at — DATETIME
  • completed_at — DATETIME
  • INDEX — idx_enrollment_user
  • INDEX — idx_enrollment_round

exams

  • exam_id — BIGINT — Primary key — NOT NULL
  • course_id — BIGINT — Foreign key → courses.course_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • pass_score — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_exam_course

lecture_progress

  • progress_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • is_completed — TINYINT(1) — NOT NULL
  • completed_at — DATETIME
  • INDEX — idx_lp_user

lectures

  • lecture_id — BIGINT — Primary key — NOT NULL
  • course_id — BIGINT — Foreign key → courses.course_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • sort_order — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_lecture_course

questions

  • question_id — BIGINT — Primary key — NOT NULL
  • quiz_id — BIGINT — Foreign key → quizzes.quiz_id
  • exam_id — BIGINT — Foreign key → exams.exam_id
  • question_text — TEXT — NOT NULL
  • score — INT — NOT NULL
  • sort_order — INT — NOT NULL
  • INDEX — idx_q_quiz
  • INDEX — idx_q_exam

quizzes

  • quiz_id — BIGINT — Primary key — NOT NULL
  • lecture_id — BIGINT — Foreign key → lectures.lecture_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • pass_score — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_quiz_lecture

users

  • user_id — BIGINT — Primary key — NOT NULL
  • employee_no — VARCHAR(20) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • department_id — INT — Foreign key → departments.department_id — NOT NULL
  • position — VARCHAR(50) — NOT NULL
  • emp_type — TINYINT — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • phone — VARCHAR(20)
  • hire_date — DATE — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

video_watch_logs

  • log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • video_id — BIGINT — Foreign key → course_videos.video_id — NOT NULL
  • watched_sec — INT — NOT NULL
  • is_completed — TINYINT(1) — NOT NULL
  • session_started_at — DATETIME
  • session_ended_at — DATETIME
  • last_watched_at — DATETIME — NOT NULL
  • INDEX — idx_watch_user