[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