MACRO NEPAL
Learning Management System (LMS) with Content-Based Recommendations
awiskar acharya
March 9, 2026
2:41 pm

Complete Full-Stack Educational Platform with PHP, MySQL, JavaScript

PROJECT OVERVIEW

A comprehensive Learning Management System with intelligent course recommendations based on user interests, learning history, and skill gaps. The platform suggests personalized learning paths and courses to help users achieve their educational goals.

Core Features

Student Features

  • ✅ User registration and authentication
  • ✅ Course browsing and search
  • ✅ Course enrollment and progress tracking
  • ✅ Video lessons with notes
  • ✅ Quizzes and assignments
  • ✅ Certificates of completion
  • ✅ Discussion forums
  • ✅ Personal learning dashboard
  • ✅ Wishlist and saved courses

Instructor Features

  • ✅ Instructor registration and approval
  • ✅ Course creation and management
  • ✅ Video lesson uploads
  • ✅ Quiz and assignment creation
  • ✅ Student progress monitoring
  • ✅ Grade assignments
  • ✅ Discussion moderation
  • ✅ Earnings dashboard

Admin Features

  • ✅ User management
  • ✅ Instructor approval
  • ✅ Category management
  • ✅ Course approval
  • ✅ Platform analytics
  • ✅ Payment management
  • ✅ Report generation

Content-Based Recommendations

  • ✅ Personalized course recommendations
  • ✅ Skill-based course suggestions
  • ✅ "Students also bought" recommendations
  • ✅ Learning path recommendations
  • ✅ Similar courses based on content
  • ✅ Popular courses in category
  • ✅ New course alerts

DATABASE SCHEMA

```sql
CREATE DATABASE lms_platform;
USE lms_platform;

-- Users table
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(100),
profile_pic VARCHAR(255),
bio TEXT,
headline VARCHAR(200),
website VARCHAR(255),
location VARCHAR(100),
user_type ENUM('student', 'instructor', 'admin') DEFAULT 'student',
is_active BOOLEAN DEFAULT TRUE,
is_verified BOOLEAN DEFAULT FALSE,
email_verified BOOLEAN DEFAULT FALSE,
last_login TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_email (email),
INDEX idx_user_type (user_type),
FULLTEXT INDEX ft_search (username, full_name, headline, bio)
);

-- User skills/interests
CREATE TABLE user_skills (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
skill_name VARCHAR(100) NOT NULL,
proficiency_level ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'beginner',
years_experience DECIMAL(3,1),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY unique_user_skill (user_id, skill_name),
INDEX idx_skill (skill_name)
);

-- User learning goals
CREATE TABLE learning_goals (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
goal_title VARCHAR(200) NOT NULL,
goal_description TEXT,
target_date DATE,
is_achieved BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user (user_id)
);

-- Categories
CREATE TABLE categories (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
slug VARCHAR(100) UNIQUE NOT NULL,
description TEXT,
icon VARCHAR(50),
parent_id INT DEFAULT NULL,
is_active BOOLEAN DEFAULT TRUE,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE,
INDEX idx_parent (parent_id),
INDEX idx_active (is_active)
);

-- Courses
CREATE TABLE courses (
id INT PRIMARY KEY AUTO_INCREMENT,
instructor_id INT NOT NULL,
category_id INT,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
subtitle VARCHAR(500),
description TEXT,
thumbnail_url VARCHAR(255),
preview_video_url VARCHAR(255),
price DECIMAL(10,2) NOT NULL,
discount_price DECIMAL(10,2),
level ENUM('beginner', 'intermediate', 'advanced', 'all-levels') DEFAULT 'beginner',
language VARCHAR(50) DEFAULT 'English',
duration_hours INT DEFAULT 0,
lessons_count INT DEFAULT 0,
students_count INT DEFAULT 0,
rating_avg DECIMAL(3,2) DEFAULT 0,
review_count INT DEFAULT 0,
requirements TEXT,
what_you_learn TEXT,
target_audience TEXT,
is_published BOOLEAN DEFAULT FALSE,
is_featured BOOLEAN DEFAULT FALSE,
is_approved BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
INDEX idx_instructor (instructor_id),
INDEX idx_category (category_id),
INDEX idx_level (level),
INDEX idx_price (price),
INDEX idx_rating (rating_avg),
INDEX idx_students (students_count),
FULLTEXT INDEX ft_course (title, subtitle, description)
);

-- Course tags/keywords
CREATE TABLE course_tags (
id INT PRIMARY KEY AUTO_INCREMENT,
course_id INT NOT NULL,
tag VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
INDEX idx_course (course_id),
INDEX idx_tag (tag)
);

-- Course prerequisites
CREATE TABLE course_prerequisites (
id INT PRIMARY KEY AUTO_INCREMENT,
course_id INT NOT NULL,
prerequisite_course_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
FOREIGN KEY (prerequisite_course_id) REFERENCES courses(id) ON DELETE CASCADE,
UNIQUE KEY unique_prerequisite (course_id, prerequisite_course_id)
);

-- Course curriculum (sections)
CREATE TABLE course_sections (
id INT PRIMARY KEY AUTO_INCREMENT,
course_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
INDEX idx_course (course_id)
);

-- Lessons
CREATE TABLE lessons (
id INT PRIMARY KEY AUTO_INCREMENT,
section_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
content_type ENUM('video', 'article', 'quiz', 'assignment') DEFAULT 'video',
content_url VARCHAR(255),
video_duration INT, -- in seconds
article_content TEXT,
is_preview BOOLEAN DEFAULT FALSE,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (section_id) REFERENCES course_sections(id) ON DELETE CASCADE,
INDEX idx_section (section_id)
);

-- Quizzes
CREATE TABLE quizzes (
id INT PRIMARY KEY AUTO_INCREMENT,
lesson_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
passing_score INT DEFAULT 70,
time_limit INT, -- in minutes
attempts_allowed INT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE,
INDEX idx_lesson (lesson_id)
);

-- Quiz questions
CREATE TABLE quiz_questions (
id INT PRIMARY KEY AUTO_INCREMENT,
quiz_id INT NOT NULL,
question TEXT NOT NULL,
question_type ENUM('multiple_choice', 'true_false', 'short_answer') DEFAULT 'multiple_choice',
points INT DEFAULT 1,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
INDEX idx_quiz (quiz_id)
);

-- Quiz options
CREATE TABLE quiz_options (
id INT PRIMARY KEY AUTO_INCREMENT,
question_id INT NOT NULL,
option_text TEXT NOT NULL,
is_correct BOOLEAN DEFAULT FALSE,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (question_id) REFERENCES quiz_questions(id) ON DELETE CASCADE
);

-- Assignments
CREATE TABLE assignments (
id INT PRIMARY KEY AUTO_INCREMENT,
lesson_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
instructions TEXT,
due_days INT, -- days after enrollment
total_points INT DEFAULT 100,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
);

-- Student enrollments
CREATE TABLE enrollments (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed_at TIMESTAMP NULL,
progress INT DEFAULT 0,
last_accessed TIMESTAMP NULL,
is_completed BOOLEAN DEFAULT FALSE,
certificate_url VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
UNIQUE KEY unique_enrollment (user_id, course_id),
INDEX idx_user (user_id),
INDEX idx_course (course_id)
);

-- Lesson progress
CREATE TABLE lesson_progress (
id INT PRIMARY KEY AUTO_INCREMENT,
enrollment_id INT NOT NULL,
lesson_id INT NOT NULL,
is_completed BOOLEAN DEFAULT FALSE,
video_position INT DEFAULT 0, -- last watched position
notes TEXT,
completed_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE,
UNIQUE KEY unique_progress (enrollment_id, lesson_id)
);

-- Quiz attempts
CREATE TABLE quiz_attempts (
id INT PRIMARY KEY AUTO_INCREMENT,
enrollment_id INT NOT NULL,
quiz_id INT NOT NULL,
score INT DEFAULT 0,
passed BOOLEAN DEFAULT FALSE,
answers JSON,
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
INDEX idx_enrollment (enrollment_id)
);

-- Assignment submissions
CREATE TABLE assignment_submissions (
id INT PRIMARY KEY AUTO_INCREMENT,
enrollment_id INT NOT NULL,
assignment_id INT NOT NULL,
submission_text TEXT,
file_url VARCHAR(255),
score INT,
feedback TEXT,
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
graded_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
FOREIGN KEY (assignment_id) REFERENCES assignments(id) ON DELETE CASCADE,
INDEX idx_enrollment (enrollment_id)
);

-- Course reviews
CREATE TABLE course_reviews (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
course_id INT NOT NULL,
rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
title VARCHAR(255),
content TEXT,
is_approved BOOLEAN DEFAULT TRUE,
helpful_count INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
UNIQUE KEY unique_review (user_id, course_id),
INDEX idx_course (course_id)
);

-- Course Q&A (discussions)
CREATE TABLE course_discussions (
id INT PRIMARY KEY AUTO_INCREMENT,
course_id INT NOT NULL,
user_id INT NOT NULL,
parent_id INT DEFAULT NULL,
title VARCHAR(255),
content TEXT NOT NULL,
is_pinned BOOLEAN DEFAULT FALSE,
is_answered BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (parent_id) REFERENCES course_discussions(id) ON DELETE CASCADE,
INDEX idx_course (course_id)
);

-- Wishlist
CREATE TABLE wishlists (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
course_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
UNIQUE KEY unique_wishlist (user_id, course_id)
);

-- Payments
CREATE TABLE payments (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
enrollment_id INT,
amount DECIMAL(10,2) NOT NULL,
payment_method VARCHAR(50) NOT NULL,
transaction_id VARCHAR(255),
status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
payment_data JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE SET NULL,
INDEX idx_user (user_id),
INDEX idx_status (status)
);

-- User course interactions (for recommendations)
CREATE TABLE user_interactions (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
course_id INT NOT NULL,
interaction_type ENUM('view', 'search', 'wishlist', 'enroll', 'complete', 'review') NOT NULL,
duration INT, -- time spent in seconds
metadata JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
INDEX idx_user (user_id),
INDEX idx_course (course_id),
INDEX idx_type (interaction_type)
);

-- User skill gaps (for recommendations)
CREATE TABLE skill_gaps (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
skill_name VARCHAR(100) NOT NULL,
current_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
desired_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'intermediate',
priority INT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user (user_id)
);

-- Course similarity (for recommendations)
CREATE TABLE course_similarity (
id INT PRIMARY KEY AUTO_INCREMENT,
course_id_1 INT NOT NULL,
course_id_2 INT NOT NULL,
similarity_score DECIMAL(5,4) NOT NULL,
based_on VARCHAR(50), -- 'category', 'tags', 'content', 'enrollments'
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (course_id_1) REFERENCES courses(id) ON DELETE CASCADE,
FOREIGN KEY (course_id_2) REFERENCES courses(id) ON DELETE CASCADE,
UNIQUE KEY unique_course_pair (course_id_1, course_id_2),
INDEX idx_course_1 (course_id_1),
INDEX idx_score (similarity_score)
);

-- Course recommendations cache
CREATE TABLE course_recommendations (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
course_id INT NOT NULL,
score DECIMAL(5,4) NOT NULL,
reason VARCHAR(50), -- 'personalized', 'trending', 'popular', 'skill_gap', 'similar'
is_viewed BOOLEAN DEFAULT FALSE,
is_clicked BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
INDEX idx_user (user_id),
INDEX idx_score (score)
);

-- Learning paths
CREATE TABLE learning_paths (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT,
category_id INT,
difficulty ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
duration_hours INT,
students_count INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
INDEX idx_category (category_id)
);

-- Learning path courses
CREATE TABLE learning_path_courses (
id INT PRIMARY KEY AUTO

Category : SOURCE CODE

Leave a Reply

Your email address will not be published. Required fields are marked *


Macro Nepal Helper