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