Scribd
Upload
216 views3 pages

LMS Database Schema

The document outlines the MySQL database schema for a Learning Management System (LMS), detailing various tables such as users, courses, chapters, and transactions. Each table includes specific columns with data types and constraints, defining the structure for user roles, course categories, content management, and financial transactions. This schema supports functionalities like user enrollment, course management, and notifications within the LMS.

Uploaded by

Shiv Rami
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
216 views3 pages

LMS Database Schema

The document outlines the MySQL database schema for a Learning Management System (LMS), detailing various tables such as users, courses, chapters, and transactions. Each table includes specific columns with data types and constraints, defining the structure for user roles, course categories, content management, and financial transactions. This schema supports functionalities like user enrollment, course management, and notifications within the LMS.

Uploaded by

Shiv Rami
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

LMS MySQL Database Schema

Table: users
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
name VARCHAR(100)
username VARCHAR(100) UNIQUE
email VARCHAR(100) UNIQUE
phone VARCHAR(20)
password VARCHAR(255)
role ENUM('Admin', 'Instructor', 'Student') NOT
NULL
status ENUM('Active', 'Inactive') DEFAULT
'Active'

Table: course_category
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
thumbnail VARCHAR(255)
title VARCHAR(100)
featured BOOLEAN
color VARCHAR(50)
status ENUM('Active', 'Inactive') DEFAULT
'Active'

Table: courses
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
course_id VARCHAR(100) UNIQUE
title VARCHAR(255)
free_publish BOOLEAN
price DECIMAL(10,2)
instructor_id INT
category_id INT
status ENUM('Published', 'Draft', 'Archived')
DEFAULT 'Draft'

Table: chapters
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
course_id INT
chapter_title VARCHAR(255)
sequence INT
content_count INT

Table: course_chapter_contents
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
course_id INT
chapter_id INT
title VARCHAR(255)
description TEXT
sequence INT
file_type ENUM('Video', 'Audio', 'Document',
'Image')
upload_option ENUM('Upload', 'Cloud Link')
is_free BOOLEAN
regular_price DECIMAL(10,2) DEFAULT 0
offer_price DECIMAL(10,2) DEFAULT 0
category_id INT

Table: coupons
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
code VARCHAR(50) UNIQUE
discount DECIMAL(5,2)
expiry_date DATE
status ENUM('Active', 'Expired') DEFAULT 'Active'

Table: enrollments
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
student_id INT
course_id INT
enrollment_date DATETIME
status ENUM('Active', 'Completed', 'Cancelled')
DEFAULT 'Active'

Table: transactions
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
user_id INT
course_id INT
amount DECIMAL(10,2)
payment_method VARCHAR(50)
transaction_date DATETIME

Table: invoices
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
student_id INT
course_id INT
amount DECIMAL(10,2)
invoice_date DATE
status ENUM('Paid', 'Unpaid') DEFAULT 'Unpaid'

Table: notifications
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
message TEXT
target_role ENUM('Admin', 'Instructor', 'Student')
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP

Table: settings
Column Name Data Type
id INT AUTO_INCREMENT PRIMARY KEY
setting_name VARCHAR(100)
value TEXT

You might also like