Database schema introduction

Jump to: navigation, search

The Moodle database has around 200 tables, and can be quite daunting at first sight. The good news is that you don't have to understand it all at once. For example, there are eight tables called forum_something. If you are interested in the forum module, then obviously you need to understand these tables, and the places they link into core tables. But if you are not interested in the forum module, you can forget about them. The same is true of each activity module. Once you take out the tables for each activity module in this way, and similarly take out the tables belonging to the enrolment plugins, question types, etc. You are left with about 50 core tables. But the good news is that even here they break down into groups that mostly you can understand together, or ignore. This page lists the core database tables in these groups. Later, it would be good to add more detailed documentation explaining some of these groups.

      The database structure is defined, edited and upgraded using the XMLDB system.

Automatically generated documentation

The install.xml files that define Moodle's database structure (are supposed to) contain comments on each table and database field, to document their purpose.

From Moodle 2.0 onwards, you can view this documentation in HTML format by going to Administration -> Development -> XMLDB editor and then clicking on the [Doc] links.

Configuration

config

"id" BIGINT NOT NULL,

"name" NVARCHAR(255) NOT NULL,

"value" NVARCHAR(-1) NOT NULL


config_log

"id" BIGINT NOT NULL,

"userid" BIGINT NOT NULL,

"timemodified" BIGINT NOT NULL,

"plugin" NVARCHAR(100) NULL,

"name" NVARCHAR(100) NOT NULL,

"value" NVARCHAR(-1) NULL,

"oldvalue" NVARCHAR(-1) NULL


config_plugins

"id" BIGINT NOT NULL,

"plugin" NVARCHAR(100) NOT NULL,

"name" NVARCHAR(100) NOT NULL,

"value" NVARCHAR(-1) NOT NULL

Users and their profiles

user

"id" BIGINT NOT NULL,

"auth" NVARCHAR(20) NOT NULL,

"confirmed" SMALLINT NOT NULL,

"policyagreed" SMALLINT NOT NULL,

"deleted" SMALLINT NOT NULL,

"suspended" SMALLINT NOT NULL,

"mnethostid" BIGINT NOT NULL,

"username" NVARCHAR(100) NOT NULL,

"password" NVARCHAR(32) NOT NULL,

"idnumber" NVARCHAR(255) NOT NULL,

"firstname" NVARCHAR(100) NOT NULL,

"lastname" NVARCHAR(100) NOT NULL,

"email" NVARCHAR(100) NOT NULL,

"emailstop" SMALLINT NOT NULL,

"icq" NVARCHAR(15) NOT NULL,

"skype" NVARCHAR(50) NOT NULL,

"yahoo" NVARCHAR(50) NOT NULL,

"aim" NVARCHAR(50) NOT NULL,

"msn" NVARCHAR(50) NOT NULL,

"phone1" NVARCHAR(20) NOT NULL,

"phone2" NVARCHAR(20) NOT NULL,

"institution" NVARCHAR(40) NOT NULL,

"department" NVARCHAR(30) NOT NULL,

"address" NVARCHAR(70) NOT NULL,

"city" NVARCHAR(120) NOT NULL,

"country" NVARCHAR(2) NOT NULL,

"lang" NVARCHAR(30) NOT NULL,

"theme" NVARCHAR(50) NOT NULL,

"timezone" NVARCHAR(100) NOT NULL,

"firstaccess" BIGINT NOT NULL,

"lastaccess" BIGINT NOT NULL,

"lastlogin" BIGINT NOT NULL,

"currentlogin" BIGINT NOT NULL,

"lastip" NVARCHAR(45) NOT NULL,

"secret" NVARCHAR(15) NOT NULL,

"picture" BIGINT NOT NULL,

"url" NVARCHAR(255) NOT NULL,

"description" NVARCHAR(-1) NULL,

"descriptionformat" SMALLINT NOT NULL,

"mailformat" SMALLINT NOT NULL,

"maildigest" SMALLINT NOT NULL,

"maildisplay" SMALLINT NOT NULL,

"htmleditor" SMALLINT NOT NULL,

"autosubscribe" SMALLINT NOT NULL,

"trackforums" SMALLINT NOT NULL,

"timecreated" BIGINT NOT NULL,

"timemodified" BIGINT NOT NULL,

"trustbitmask" BIGINT NOT NULL,

"imagealt" NVARCHAR(255) NULL,

"screenreader" SMALLINT NOT NULL

user_enrolments

"id" BIGINT NOT NULL,

"status" BIGINT NOT NULL,

"enrolid" BIGINT NOT NULL,

"userid" BIGINT NOT NULL,

"timestart" BIGINT NOT NULL,

"timeend" BIGINT NOT NULL,

"modifierid" BIGINT NOT NULL,

"timecreated" BIGINT NOT NULL,

"timemodified" BIGINT NOT NULL

user_info_category

"id" BIGINT NOT NULL,

"name" NVARCHAR(255) NOT NULL,

"sortorder" BIGINT NOT NULL

user_info_data

"id" BIGINT NOT NULL,

"userid" BIGINT NOT NULL,

"fieldid" BIGINT NOT NULL,

"data" NVARCHAR(-1) NOT NULL,

"dataformat" SMALLINT NOT NULL

user_info_field

"id" BIGINT NOT NULL,

"shortname" NVARCHAR(255) NOT NULL,

"name" NVARCHAR(-1) NOT NULL,

"datatype" NVARCHAR(255) NOT NULL,

"description" NVARCHAR(-1) NULL,

"descriptionformat" SMALLINT NOT NULL,

"categoryid" BIGINT NOT NULL,

"sortorder" BIGINT NOT NULL,

"required" SMALLINT NOT NULL,

"locked" SMALLINT NOT NULL,

"visible" SMALLINT NOT NULL,

"forceunique" SMALLINT NOT NULL,

"signup" SMALLINT NOT NULL,

"defaultdata" NVARCHAR(-1) NULL,

"defaultdataformat" SMALLINT NOT NULL,

"param1" NVARCHAR(-1) NULL,

"param2" NVARCHAR(-1) NULL,

"param3" NVARCHAR(-1) NULL,

"param4" NVARCHAR(-1) NULL,

"param5" NVARCHAR(-1) NULL

user_lastaccess 
this is separated from the user table for performance reasons

"id" BIGINT NOT NULL,

"userid" BIGINT NOT NULL,

"courseid" BIGINT NOT NULL,

"timeaccess" BIGINT NOT NULL

user_preferences

"id" BIGINT NOT NULL,

"userid" BIGINT NOT NULL,

"name" NVARCHAR(255) NOT NULL,

"value" NVARCHAR(1333) NOT NULL

user_private_key

"id" BIGINT NOT NULL,

"script" NVARCHAR(128) NOT NULL,

"value" NVARCHAR(128) NOT NULL,

"userid" BIGINT NOT NULL,

"instance" BIGINT NULL,

"iprestriction" NVARCHAR(255) NULL,

"validuntil" BIGINT NULL,

"timecreated" BIGINT NULL


ER Diagram of the Users and profiles tables / EER Diagram of the User tables with their relations

The roles and capabilities system

role
defines a role, its name, etc. Other parts of the role definition are stored in the role_capabilities and role_context_levels tables.
capabilites
the various permissions that can be granted.
context
a context is a scope in Moodle, for example the whole system, a course, a particular activity. The type is given by contextlevel, and depending on context level, instanceid points to one of a number of different tables.
role_allow_assign
which roles can assign which other roles
role_allow_override
which roles can override which other roles
role_allow_switch
which roles can switch to which other roles (Moodle 2.0 onwards.)
role_assignments
which users are assigned which roles in which contexts
role_capabilities
the permission for each capability in either a role definition (if contextid points to the system context) or a role override (if contextid points to some other context)
role_context_levels
the context levels at which each role can be assigned. (Moodle 2.0 onwards)
role_names
used to implement the feature where roles can be given different names in different courses (or, more gererally, contexts)
role_sortorder
is not actually used anywhere in the code!

The following diagram shows the tables with their columns and the relationships between them, and other tables in Moodle.

RolesDatabase.png

Courses and their organisation into categories

course
course_categories
course_display
course_meta
course_request

Activities and their arrangement within courses

Course modules database.png

(Here is the Dia file, should you need to edit this image: File:Course modules database.dia.)

modules
course_allowed_modules
course_modules
course_sections

See also the tables belonging to particular activity modules. For example forum_*, quiz_*, etc.

Groups and groupings

groups

"id" BIGINT NOT NULL,

"courseid" BIGINT NOT NULL,

"name" NVARCHAR(254) NOT NULL,

"description" NVARCHAR(-1) NULL,

"descriptionformat" SMALLINT NOT NULL,

"enrolmentkey" NVARCHAR(50) NULL,

"picture" BIGINT NOT NULL,

"hidepicture" SMALLINT NOT NULL,

"timecreated" BIGINT NOT NULL,

"timemodified" BIGINT NOT NULL,

"idnumber" NVARCHAR(100) NOT NULL

groups_members

"id" BIGINT NOT NULL,

"groupid" BIGINT NOT NULL,

"userid" BIGINT NOT NULL,

"timeadded" BIGINT NOT NULL

groupings

"id" BIGINT NOT NULL,

"courseid" BIGINT NOT NULL,

"name" NVARCHAR(255) NOT NULL,

"description" NVARCHAR(-1) NULL,

"descriptionformat" SMALLINT NOT NULL,

"configdata" NVARCHAR(-1) NULL,

"timecreated" BIGINT NOT NULL,

"timemodified" BIGINT NOT NULL,

"idnumber" NVARCHAR(100) NOT NULL

groupings_groups

"id" BIGINT NOT NULL,

"groupingid" BIGINT NOT NULL,

"groupid" BIGINT NOT NULL,

"timeadded" BIGINT NOT NULL

ER Diagram of the Groups system

The logging system

log
log_display

Blocks system

block
block_instance
block_rss_client
block_pinned
block_search_documents

Some particular blocks also have their own database tables. For example block_rss_client. See:

Events

event
events_handlers
events_queue
events_queue_handlers

Backup and restore

backup_config
backup_courses
backup_files
backup_ids
backup_log

ER Diagram of the Backup system

Statistics

stats_daily
stats_monthly
stats_user_daily
stats_user_monthly
stats_user_weekly
stats_weekly

Tags

tag_*

Gradebook

grade_*

ER Diagram of the Gradebook system

Question bank and question engine

Note that the tables for the core question bank, and also the tables for the individual question types, all have names starting with question_, however, it is easier to think of them separately.

question
question_answers
question_attempts
question_categories
question_sessions
question_states

See below for a list of question type tables.

Messaging system

message

"id" BIGINT NOT NULL,

"useridfrom" BIGINT NOT NULL,

"useridto" BIGINT NOT NULL,

"subject" NVARCHAR(-1) NULL,

"fullmessage" NVARCHAR(-1) NULL,

"fullmessageformat" SMALLINT NULL,

"fullmessagehtml" NVARCHAR(-1) NULL,

"smallmessage" NVARCHAR(-1) NULL,

"notification" SMALLINT NULL,

"contexturl" NVARCHAR(-1) NULL,

"contexturlname" NVARCHAR(-1) NULL,

"timecreated" BIGINT NOT NULL


message_contacts

"id" BIGINT NOT NULL,

"userid" BIGINT NOT NULL,

"contactid" BIGINT NOT NULL,

"blocked" SMALLINT NOT NULL

message_processors

"id" BIGINT NOT NULL,

"name" NVARCHAR(166) NOT NULL,

"enabled" SMALLINT NOT NULL


message_providers

"id" BIGINT NOT NULL,

"name" NVARCHAR(100) NOT NULL,

"component" NVARCHAR(200) NOT NULL,

"capability" NVARCHAR(255) NULL


message_read

"id" BIGINT NOT NULL,

"useridfrom" BIGINT NOT NULL,

"useridto" BIGINT NOT NULL,

"subject" NVARCHAR(-1) NULL,

"fullmessage" NVARCHAR(-1) NULL,

"fullmessageformat" SMALLINT NULL,

"fullmessagehtml" NVARCHAR(-1) NULL,

"smallmessage" NVARCHAR(-1) NULL,

"notification" SMALLINT NULL,

"contexturl" NVARCHAR(-1) NULL,

"contexturlname" NVARCHAR(-1) NULL,

"timecreated" BIGINT NOT NULL,

"timeread" BIGINT NOT NULL


message_working

"id" BIGINT NOT NULL,

"unreadmessageid" BIGINT NOT NULL,

"processorid" BIGINT NOT NULL

Moodle Network

mnet_application
mnet_enrol_assignments
mnet_enrol_course
mnet_host
mnet_host2service
mnet_log
mnet_rpc
mnet_service
mnet_service2rpc
mnet_session
mnet_sso_access_control

ER Diagram of the MNet system

Caching

cache_filters
cache_flags
cache_text

Miscellaneous

scale
scale_history
sessions2
timezone
post

Activity modules

  • Assignment
  • Chat
  • Choice
  • Data
  • Feedback
  • Forum
  • Glossary
  • Hotpot
  • Label
  • Lesson
  • Quiz
  • Resource
  • SCORM
  • Survey
  • Wiki
  • Workshop

Authentication plugins

  • ...

Blocks

block
block_instance
block_pinned
block_rss_client
block__search_documents

Enrolment plugins

  • ...

Question types

See also