Database Structure
Overview
Pluto LMS stores all LMS data in an SQL database on a secure server. The Custom Reports tool provided by Pluto LMS allows you to query this database in order to create custom reports as required by your organisation.
Note:
Pluto LMS does not offer services to build the queries for you. Please consult a 3rd party consultant or use an in-house SQL developer if you are not able to build SQL queries. (Guides to SQL here)
Pluto LMS does not offer services to build the queries for you. Please consult a 3rd party consultant or use an in-house SQL developer if you are not able to build SQL queries. (Guides to SQL here)
Visualising the Pluto LMS Database:
The below graphic will help you visualize how each database table connects to the other. Some important notes:
- This is a simplified graphic and in many instances you can have more than one connection across tables. Each connection was not included otherwise the graphic would become difficult to read.
- The blue boxes represent database tables that primarily link to user data.
- The red boxes represent database tables that primary link to course data.
Each table contains columns and rows. The sample data below will help you understand what information is included in each table.
Database Tables:
Note: The tables below are sample data (with limited numbers of rows shown). Your actual LMS instance will contain more data. We have also only included the important or useful columns required to create custom reports.
When creating an SQL query, the table name will need to be referenced when querying data from the database. These will be used in your FROM statement within your SQL SELECT query. Click here to learn more.
Unless stated otherwise: 0 often represents "False" or "No" (i.e., the condition is not met), whereas 1 often represents "True" or "Yes" (i.e., the condition is met).
prefix_user:
Table of all users on the LMS with associated information. This table is used to store all user information.
id: A system generated unique identifier for the specific user (primary key)
auth: The authentication method assigned to the user which grants them access to the LMS
deleted: 1 = User profile has been deleted, 0 = User profile is active
suspended: 1 = User profile has been suspended, 0 = User profile is active
username: Unique username of the user
firstname: The first name of the user
lastname: The last name of the user
email: The email address of the user
city: The city of the user
country: The country of the user
firstaccess: The first time the user logged into the site (Unix)
timecreated: The time the user created on the site (Unix)
timemodified The time the user was modified the site (Unix)
prefix_user_info_field:
This table contains a list of all user profile fields. This table is used to link custom profile field information to the user profile.
id: A system generated unique identifier for the profile field (primary key)
shortname: The shortname of the profile field
name: The full name of the profile field
prefix_user_info_data:
This table contains the field values for each user. This table is used to link user specific field values to the custom profile field.
id: A system generated unique identifier for the field value (primary key)
userid: The id of the system user (foreign key: id column in prefix_user)
fieldid: The profile field id (foreign key: id column in prefix_user_info_field)
data: The profile field value
prefix_user_lastaccess:
This table contains when each user last accessed a particular course. This table can be used to see when a user last accessed a course.
id: A system generated unique identifier for the last course access of user (primary key)
userid: The id of the system user (foreign key: id column in prefix_user)
courseid: The id of the course (foreign key: id column in prefix_course)
timeaccess: The last time the user viewed the course (Unix)
prefix_course:
Table of courses on LMS with associated information. When a new course is created, it gets added to this table so that you can view course details.
id: A system generated unique identifier for the specific course (primary key)
category: The course category id.
fullname: The fullname of the course
shortname: The fullname of the course
startdate: The start time of the course (Unix)
enddate: The end time of the course (Unix)
timecreated: The time the course was created (Unix)
timemodified: The time the course was modified (Unix)
prefix_edwreports_course_progress:
Table of completed modules within course, course progress and completion time for each course per user. This table can be used to report on user course progress as a percentage as well as identify when they completed that particular course.
id: A system generated unique identifier for the course progress data (primary key)
courseid: The id of the course (foreign key: id column in prefix_course)
userid: The id of the system user (foreign key: id column in prefix_user)
completedmodules: The ids of completed activities within that course (foreign key: id column in prefix_course_modules)
totalmodules: The total amount of activities within that course
progress: The percentage of activities completed within that course
completiontime: The time the course was completed (Unix)
prefix_logstore_standard_log:
Table of all logs on LMS site. Every action taken on your LMS site will be listed here. Logs are not erased.
id: A system generated unique identifier for the log (primary key)
eventname: The action that took place for this log
component: Where the log was actioned
action: The specific action associated to the log
target: The specific target associated to the action
userid: The user id associated to the action (foreign key: id column in prefix_user)
courseid: The course id associated to the action (foreign key: id column in prefix_course)
timecreated: The time the log was created (Unix)
ip: The IP address associated to the log
prefix_cohort:
Table of all user segments and associated information. Note that the database "cohort" reference = "user segment" on the LMS interface.
id: A system generated unique identifier for the user segment (primary key)
name: The name of the user segment
timecreated: The time the user segment was created (Unix)
prefix_cohort_members:
Table of all user segment members and associated information. This table links users to the respective user segment.
id: A system generated unique identifier for the specific user within the user segment (primary key)
cohortid: The ID of the user segment (foreign key: id column in prefix_cohort)
userid: The ID of the system user within that user segment (foreign key: id column in prefix_user)
timeadded: The time that the user was added to the user segment (Unix)
prefix_course_completions:
Table of users and their course completion times.
id: A system generated unique identifier for the course completion (primary key)
userid: The ID of the system user (foreign key: id column in prefix_user)
course: The ID of the course (foreign key: id column in prefix_course)
timeenrolled: The time the user enrolled into the course (Unix)
timecompleted: The time the user completed the course (Unix)
prefix_course_modules:
Table of modules (activities) associated to courses. Note that the database "module" reference = an "activity" on the LMS interface. These are the activities within your courses.
id: A system generated unique identifier for the course activity (primary key)
course: The ID of the course (foreign key: id column in prefix_course)
module: The ID of the course activity type (foreign key: id column in prefix_modules)
instance: The instance ID of the specific activity
added: The time the activity was added to the course (Unix)
visible: 1 = Activity is visible to learners, 0 = Activity is not visible
groupmode: 1 = The activity group mode is on, 0 = The activity group mode is off
completion: 1 or 2 = The completion settings have been enabled for this activity and 0 = completion settings are off
completionview: 1 = The completion view has been enabled for this activity and 0 = completion view is off
prefix_modules:
Table of activity types and associated information.
id: A system generated unique identifier for the activity type (primary key)
name: The name of the activity type
prefix_course_modules_completion:
Table of activity completion information. This table links users to course activity completions.
id: A system generated unique identifier for the specific activity completion (primary key)
coursemoduleid: The ID of the activity (foreign key: id column in prefix_course_modules)
userid: The ID of the system user (foreign key: id column in prefix_user)
completionstate: 0 = Activity is not complete (pass not specified), 1 = Activity is complete (pass not specified), 2 = Activity is complete (with pass), 3 = Activity is complete (not passed) [The pass criteria is defined within the specific activity settings]
viewed: 1 = Activity has been viewed by the user, 0 = Activity has not been viewed by the user
timemodified: The time the activity completion or view was modified (Unix)
prefix_enrol:
Table of all enrollment types. This table displays the different types of enrollments setup for each LMS course.
id: A system generated unique identifier for the enrollment type (primary key)
enrol: The type of enrollment
courseid: The ID of the course to which the enrollment is associated (foreign key: id column in prefix_course)
enrolstartdate: The enrollment start date for that course (Unix)
enrolenddate: The enrollment end date for that course (Unix)
roleid: The role ID associated to the enrollment (foreign key: id column in prefix_role)
timecreated: The time the enrollment type was created in that course (Unix)
timemodified: The time the enrollment type was modified in that course (Unix)
prefix_user_enrolments:
Table associating users to the enrollment types. This table links users to the specific course enrollments.
id: A system generated unique identifier for the user enrollments (primary key)
enrolid: The associated enrollment id (foreign key: id column in prefix_enrol)
userid: The ID of the system user (foreign key: id column in prefix_user)
timestart: The start time of the enrollment for that user (Unix)
timeend: The end time of the enrollment for that user (Unix)
timecreated: The time the enrollment was created (Unix)
timemodified: The time the enrollment was modified (Unix)
prefix_event:
Table of all calendar events within your LMS. These events can be found on the LMS calendar interface.
prefix_scorm:
Table of all SCORM files within your LMS.
prefix_scorm_scoes_track:
Table of all SCORM data with user association.
prefix_context:
A list of contexts to an instance ID.
prefix_badge:
Table of all badges on your LMS.
prefix_badge_issued:
Table of all badges linked to users.
prefix_role:
Table of all roles on your LMS.
id: A system generated unique identifier for the specific role (primary key)
name: The name of the role
shortname: The shortname of the role
description: The description of the role
prefix_role_assignments:
Table of all roles assigned to users. This table links users to the role within context.
id: A system generated unique identifier for the role assignment (primary key)
roleid: The ID of the system role (foreign key: id column in prefix_role)
contextid: The context ID of the role assignment (foreign key: id column in prefix_context)
userid: The ID of the system user (foreign key: id column in prefix_user)
timemodified: The time the role assignment was modified
prefix_grade_grades:
Table of all grades assigned to users.
prefix_grade_items:
Table of all graded items within your course. These grade items can be found within the course gradebook via the LMS interface.
prefix_groups:
Table of all groups within your LMS courses.
id: A system generated unique identifier for the group (primary key)
courseid: The course ID (foreign key: id column in prefix_course)
name: The name of the course group
timecreated: The time the course group was created (Unix)
timemodified: The time the course group was modified (Unix)
prefix_groups_members:
Table of users and their associated groups.
id: A system generated unique identifier for the group members (primary key)
groupid: The ID of the course group (foreign key: id column in prefix_groups)
userid: The ID of the user (foreign key: id column in prefix_user)
timeadded: The time the user was added to the course group
prefix_data:
Table of all data tables linked to courses.
id: A system generated unique identifier for the data table (primary key)
course: The ID of the course where the data table is located (foreign key: id column in prefix_course)
name: The name of the data table
timemodified: The time the data table was last modified
prefix_data_fields:
Table of all data table fields created within data tables.
id: A system generated unique identifier for the data table fields (primary key)
dataid: The ID of the data table to which the field is associated (foreign key: id column in prefix_data)
type: The type of field
name: The field name
timemodified: The time the field was modified (Unix)
prefix_data_records:
Table of all data table records and associated user.
id: A system generated unique identifier for the data table records (primary key)
userid: The ID of the system user (foreign key: id column in prefix_user)
dataid: The ID of the data table to which the record is associated (foreign key: id column in prefix_data)
timecreated: The time the record was created (Unix)
timemodified: The time the record was modified (Unix)
prefix_data_content:
Table of all data table field inputs and associated fields.
id: A system generated unique identifier for the data table field values (primary key)
fieldid: The ID of the field (foreign key: id column in prefix_data_fields)
recordid: The ID of the record (foreign key: id column in prefix_data_records)
content: The field value
Related Articles
Moving Gradebook Items
OVERVIEW Moving grade book items in an LMS used for internal staff training refers to the process of rearranging or reorganizing the assessment components within the grade book. This feature allows instructors, trainers, or administrators to change ...