[AIARA] LearningCrew
Tables (37)
Assignment_Submissions
- submission_id — INT — Primary key — NOT NULL
- assignment_id — INT — Foreign key → Assignments.assignment_id — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- file_id — INT — Foreign key → Files.file_id — NOT NULL
- submitted_at — DATETIME(6) — NOT NULL
- grade — DECIMAL(5, 2)
- feedback — TEXT
- INDEX — `FK_submission_assignment`
- INDEX — `FK_submission_user`
- INDEX — `FK_submission_file`
Assignments
- assignment_id — INT — Primary key — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- description — TEXT
- due_date — DATETIME(6)
- INDEX — `FK_assignment_course`
Cart_Items
- item_id — INT — Primary key — NOT NULL
- cart_id — INT — Foreign key → Shopping_Cart.cart_id — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- added_at — DATETIME(6) — NOT NULL
- INDEX — `FK_cart_item_cart`
- INDEX — `FK_cart_item_course`
Categories
- category_id — INT — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- parent_category_id — INT — Foreign key → Categories.category_id
- INDEX — `FK_category_parent`
Certificates
- certificate_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- issue_date — DATETIME(6) — NOT NULL
- INDEX — `FK_certificate_user`
- INDEX — `FK_certificate_course`
Comments
- comment_id — INT — Primary key — NOT NULL
- content — TEXT — NOT NULL
- author_id — INT — Foreign key → Users.user_id — NOT NULL
- post_id — INT — Foreign key → Posts.post_id — NOT NULL
- created_at — DATETIME(6) — NOT NULL
- INDEX — `FK_comment_author`
- INDEX — `FK_comment_post`
Communities
- community_id — INT — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- description — TEXT
- visibility — ENUM('public', 'private') — NOT NULL
- created_at — DATETIME(6) — NOT NULL
Community_Memberships
- membership_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- community_id — INT — Foreign key → Communities.community_id — NOT NULL
- joined_at — DATETIME(6) — NOT NULL
- INDEX — `FK_community_member_user`
- INDEX — `FK_community_member_community`
Course_Content_Metadata
- content_id — INT — Primary key — NOT NULL
- lesson_id — INT — Foreign key → Lessons.lesson_id — NOT NULL
- content_type — ENUM('video', 'document', 'link', 'text') — NOT NULL
- duration — INT
- url — VARCHAR(255)
- description — TEXT
- INDEX — `FK_content_lesson`
Course_Schedules
- schedule_id — INT — Primary key — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- session_type — ENUM('live', 'offline') — NOT NULL
- start_time — DATETIME(6) — NOT NULL
- end_time — DATETIME(6) — NOT NULL
- location — VARCHAR(255)
- INDEX — `FK_schedule_course`
Courses
- course_id — INT — Primary key — NOT NULL
- title — VARCHAR(255) — NOT NULL
- description — TEXT
- category_id — INT — Foreign key → Categories.category_id — NOT NULL
- price — DECIMAL(10, 2)
- duration — VARCHAR(50)
- created_at — DATETIME(6) — NOT NULL
- INDEX — `FK_course_category`
Departments
- department_id — INT — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- manager_id — INT — Foreign key → Users.user_id — NOT NULL
- INDEX — `FK_dept_manager`
Enrollments
- enrollment_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- enrollment_date — DATETIME(6) — NOT NULL
- status — ENUM('enrolled', 'completed', 'pending') — NOT NULL
- INDEX — `FK_enrollment_user`
- INDEX — `FK_enrollment_course`
Feedback
- feedback_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- rating — INT — NOT NULL
- comment — TEXT
- submitted_at — DATETIME(6) — NOT NULL
- INDEX — `FK_feedback_user`
- INDEX — `FK_feedback_course`
Files
- file_id — INT — Primary key — NOT NULL
- name — VARCHAR(255) — NOT NULL
- type — VARCHAR(50) — NOT NULL
- size — INT — NOT NULL
- upload_date — DATETIME(6) — NOT NULL
- course_id — INT — Foreign key → Courses.course_id
- post_id — INT — Foreign key → Posts.post_id
- INDEX — `FK_file_course`
- INDEX — `FK_file_post`
Group_Memberships
- membership_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- group_id — INT — Foreign key → Groups.group_id — NOT NULL
- joined_at — DATETIME(6) — NOT NULL
- INDEX — `FK_group_member_user`
- INDEX — `FK_group_member_group`
Groups
- group_id — INT — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- description — TEXT
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- created_at — DATETIME(6) — NOT NULL
- INDEX — `FK_group_course`
Learning_Plans
- plan_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- description — TEXT
- created_at — DATETIME(6) — NOT NULL
- INDEX — `FK_plan_user`
Lessons
- lesson_id — INT — Primary key — NOT NULL
- module_id — INT — Foreign key → Modules.module_id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- content — TEXT
- order_index — INT — NOT NULL
- INDEX — `FK_lesson_module`
Live_Sessions
- session_id — INT — Primary key — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- session_date — DATETIME(6) — NOT NULL
- link — VARCHAR(255) — NOT NULL
- schedule_id — INT — Foreign key → Course_Schedules.schedule_id
- INDEX — `FK_live_session_course`
- INDEX — `FK_live_session_schedule`
Modules
- module_id — INT — Primary key — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- description — TEXT
- order_index — INT — NOT NULL
- INDEX — `FK_module_course`
Notifications
- notification_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- message — TEXT — NOT NULL
- created_at — DATETIME(6) — NOT NULL
- status — ENUM('read', 'unread') — NOT NULL
- INDEX — `FK_notification_user`
Offline_Sessions
- session_id — INT — Primary key — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- session_date — DATETIME(6) — NOT NULL
- location — VARCHAR(255) — NOT NULL
- schedule_id — INT — Foreign key → Course_Schedules.schedule_id
- INDEX — `FK_offline_session_course`
- INDEX — `FK_offline_session_schedule`
Payments
- payment_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- amount — DECIMAL(10, 2) — NOT NULL
- payment_date — DATETIME(6) — NOT NULL
- status — ENUM('completed', 'pending', 'failed') — NOT NULL
- cart_id — INT — Foreign key → Shopping_Cart.cart_id
- INDEX — `FK_payment_user`
- INDEX — `FK_payment_course`
- INDEX — `FK_payment_cart`
Permissions
- permission_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- resource_type — ENUM('course', 'community', 'group') — NOT NULL
- resource_id — INT — NOT NULL
- permission_level — ENUM('read', 'write', 'admin') — NOT NULL
- INDEX — `FK_permission_user`
Points
- point_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- activity_type — VARCHAR(100) — NOT NULL
- points_earned — INT — NOT NULL
- awarded_at — DATETIME(6) — NOT NULL
- INDEX — `FK_point_user`
Posts
- post_id — INT — Primary key — NOT NULL
- title — VARCHAR(255) — NOT NULL
- content — TEXT
- author_id — INT — Foreign key → Users.user_id — NOT NULL
- community_id — INT — Foreign key → Communities.community_id — NOT NULL
- course_id — INT — Foreign key → Courses.course_id
- created_at — DATETIME(6) — NOT NULL
- INDEX — `FK_post_author`
- INDEX — `FK_post_community`
- INDEX — `FK_post_course`
Quiz_Questions
- question_id — INT — Primary key — NOT NULL
- quiz_id — INT — Foreign key → Quizzes.quiz_id — NOT NULL
- question_text — TEXT — NOT NULL
- question_type — ENUM('multiple_choice', 'true_false', 'short_answer') — NOT NULL
- correct_answer — TEXT
- INDEX — `FK_question_quiz`
Quiz_Responses
- response_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- question_id — INT — Foreign key → Quiz_Questions.question_id — NOT NULL
- quiz_id — INT — Foreign key → Quizzes.quiz_id — NOT NULL
- response_text — TEXT — NOT NULL
- is_correct — BOOLEAN — NOT NULL
- submitted_at — DATETIME(6) — NOT NULL
- INDEX — `FK_response_user`
- INDEX — `FK_response_question`
- INDEX — `FK_response_quiz`
Quizzes
- quiz_id — INT — Primary key — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- title — VARCHAR(255) — NOT NULL
- description — TEXT
- due_date — DATETIME(6)
- time_limit — INT
- passing_score — INT
- attempts_allowed — INT
- INDEX — `FK_quiz_course`
Shopping_Cart
- cart_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- created_at — DATETIME(6) — NOT NULL
- INDEX — `FK_cart_user`
Tutor_Course_Assignments
- assignment_id — INT — Primary key — NOT NULL
- tutor_id — INT — Foreign key → Tutors.tutor_id — NOT NULL
- course_id — INT — Foreign key → Courses.course_id — NOT NULL
- assigned_at — DATETIME(6) — NOT NULL
- INDEX — `FK_tutor_assignment_tutor`
- INDEX — `FK_tutor_assignment_course`
Tutors
- tutor_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id
- name — VARCHAR(100) — NOT NULL
- is_ai — BOOLEAN — NOT NULL
- description — TEXT
- availability_status — ENUM('available', 'unavailable') — NOT NULL
- INDEX — `FK_tutor_user`
User_Activity_Log
- log_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- activity_type — ENUM('login', 'course_view', 'quiz_attempt', 'comment_posted', 'payment_made') — NOT NULL
- activity_details — TEXT
- timestamp — DATETIME(6) — NOT NULL
- INDEX — `FK_activity_log_user`
User_Departments
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- department_id — INT — Foreign key → Departments.department_id — NOT NULL
- INDEX — `FK_user_dept_user`
- INDEX — `FK_user_dept_dept`
User_Progress
- progress_id — INT — Primary key — NOT NULL
- user_id — INT — Foreign key → Users.user_id — NOT NULL
- lesson_id — INT — Foreign key → Lessons.lesson_id — NOT NULL
- status — ENUM('not_started', 'in_progress', 'completed') — NOT NULL
- last_accessed — DATETIME(6) — NOT NULL
- INDEX — `FK_progress_user`
- INDEX — `FK_progress_lesson`
Users
- user_id — INT — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- email — VARCHAR(100) — NOT NULL
- password — VARCHAR(255) — NOT NULL
- role — ENUM('learner', 'admin', 'instructor', 'department_head') — NOT NULL
- status — ENUM('active', 'inactive', 'pending', 'rejected') — NOT NULL
- phone — VARCHAR(20)
- gender — ENUM('male', 'female', 'other')
- birth_date — DATE
- nationality — VARCHAR(50)
- created_at — DATETIME(6) — NOT NULL
- updated_at — DATETIME(6) — NOT NULL