MoodleDocs - User contributions [en]

Database schema introduction

2012-08-21T12:29:47Z

Svbrajudatla:

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_Documentation|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
;config_plugins

==Users and their profiles==

;user
;user_info_category
;user_info_field
;user_info_data
;user_lastaccess : this is separated from the user table for performance reasons
;user_preferences
;user_private_key

[[Media:Users_and_profiles_erd.png|ER Diagram of the Users and profiles tables]]
/
[[Media:user-tables-with-relations.png|EER Diagram of the User tables with their relations]]

==[[Roles|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.

[[Image:RolesDatabase.png|thumb|left|900px]]

==Courses and their organisation into categories==

;course
;course_categories
;course_display
;course_meta
;course_request

==Activities and their arrangement within courses==

[[Image:Course_modules_database.png]]

(Here is the Dia file, should you need to edit this image: [[Image: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_database_structure|quiz_*]], etc.

==[[Groups|Groups and groupings]]==

;groups
;groups_members
;groupings
;groupings_groups

[[Media:Groups_erd.png|ER Diagram of the Groups system]]

==The logging system==

;log
;log_display

==[[Blocks#Creating_Database_Tables_for_Blocks|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:
* [[Media:Blocks_erd.png|ER Diagram of the Blocks system]]
* [[Blocks#Database support]]
* [http://dev.moodle.org/mod/resource/view.php?id=48 Unit 7 - Part A - Block Basics] of the Introduction to Moodle Programming course.

==[[Events|Events]]==

;event
;events_handlers
;events_queue
;events_queue_handlers

==Backup and restore==

;backup_config
;backup_courses
;backup_files
;backup_ids
;backup_log
[[Media:Backups_erd.png|ER Diagram of the Backup system]]

==Statistics==

;stats_daily
;stats_monthly
;stats_user_daily
;stats_user_monthly
;stats_user_weekly
;stats_weekly

==Tags==

;tag_*

==[[Grades|Gradebook]]==

;grade_*
[[Media:Gradebook_erd.png|ER Diagram of the Gradebook system]]

==[[Question_database_structure|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

[[#Question_types|See below for a list of question type tables]].

==Messaging system==

;message_*

==[[Moodle_Network|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
[[Media:mnet_erd.png|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_database_structure|Quiz]]
* Resource
* SCORM
* Survey
* Wiki
* [[Workshop#Database_structure|Workshop]]

==Authentication plugins==

* ...

==Blocks==

;block
;block_instance
;block_pinned
;block_rss_client
;block__search_documents

==Enrolment plugins==

* ...

==Question types==

* [[Calculated_question_type#Database_tables|Calculated]]
* [[Matching_question_type#Database_tables|Matching]]
* [[Multiple_Choice_question_type#quiz_multichoice_table|Multiple choice]]
* [[Cloze_question_type#Data_structure|Multi-answer (Cloze)]]
* [[Numerical_question_type#Database_tables|Numerical]]
* [[Random_Short-Answer_Matching_question_type#Database_tables|Random short-answer matching]]
* [[Short_Answer_question_type#Database_tables|Short answer]]
* [[True/False_question_type#Database_tables|True/false]]

==See also==
* [[Media:Moodle2erd.png|ER Diagram of the installed system ]] - a single big png image
* [[Database_Schema]] - some automatically generated diagrams.
* [[Database FAQ]]
* [[XMLDB_Documentation]] - the XMLDB system used to define, edit, and update the database structure.
* [http://www.examulator.com/er Moodle 2.2 ER Diagram] by Marcus Green
* [http://www.examulator.com/er/er_howto.html Moodle ER Diagram HowTo]: Creating a Moodle ER Diagram by generating a log from the install process

[[Category:XMLDB]]

Database schema introduction

2012-08-21T12:26:27Z

Svbrajudatla:

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.
A database schema (/ˈski.mə/SKEE-ma) of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data to create a blueprint of how a database will be constructed (divided into database tables). The formal definition of database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language. A database can be considered a structure in realization of the database language.[1] The states of a created conceptual schema are transformed into an explicit mapping, the database schema. This describes how real world entities are modeled in the database
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 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.

The database structure is defined, edited and upgraded using the [[XMLDB_Documentation|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
;config_plugins

==Users and their profiles==

;user
;user_info_category
;user_info_field
;user_info_data
;user_lastaccess : this is separated from the user table for performance reasons
;user_preferences
;user_private_key

[[Media:Users_and_profiles_erd.png|ER Diagram of the Users and profiles tables]]
/
[[Media:user-tables-with-relations.png|EER Diagram of the User tables with their relations]]

==[[Roles|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.

[[Image:RolesDatabase.png|thumb|left|900px]]

==Courses and their organisation into categories==

;course
;course_categories
;course_display
;course_meta
;course_request

==Activities and their arrangement within courses==

[[Image:Course_modules_database.png]]

(Here is the Dia file, should you need to edit this image: [[Image: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_database_structure|quiz_*]], etc.

==[[Groups|Groups and groupings]]==

;groups
;groups_members
;groupings
;groupings_groups

[[Media:Groups_erd.png|ER Diagram of the Groups system]]

==The logging system==

;log
;log_display

==[[Blocks#Creating_Database_Tables_for_Blocks|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:
* [[Media:Blocks_erd.png|ER Diagram of the Blocks system]]
* [[Blocks#Database support]]
* [http://dev.moodle.org/mod/resource/view.php?id=48 Unit 7 - Part A - Block Basics] of the Introduction to Moodle Programming course.

==[[Events|Events]]==

;event
;events_handlers
;events_queue
;events_queue_handlers

==Backup and restore==

;backup_config
;backup_courses
;backup_files
;backup_ids
;backup_log
[[Media:Backups_erd.png|ER Diagram of the Backup system]]

==Statistics==

;stats_daily
;stats_monthly
;stats_user_daily
;stats_user_monthly
;stats_user_weekly
;stats_weekly

==Tags==

;tag_*

==[[Grades|Gradebook]]==

;grade_*
[[Media:Gradebook_erd.png|ER Diagram of the Gradebook system]]

==[[Question_database_structure|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

[[#Question_types|See below for a list of question type tables]].

==Messaging system==

;message_*

==[[Moodle_Network|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
[[Media:mnet_erd.png|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_database_structure|Quiz]]
* Resource
* SCORM
* Survey
* Wiki
* [[Workshop#Database_structure|Workshop]]

==Authentication plugins==

* ...

==Blocks==

;block
;block_instance
;block_pinned
;block_rss_client
;block__search_documents

==Enrolment plugins==

* ...

==Question types==

* [[Calculated_question_type#Database_tables|Calculated]]
* [[Matching_question_type#Database_tables|Matching]]
* [[Multiple_Choice_question_type#quiz_multichoice_table|Multiple choice]]
* [[Cloze_question_type#Data_structure|Multi-answer (Cloze)]]
* [[Numerical_question_type#Database_tables|Numerical]]
* [[Random_Short-Answer_Matching_question_type#Database_tables|Random short-answer matching]]
* [[Short_Answer_question_type#Database_tables|Short answer]]
* [[True/False_question_type#Database_tables|True/false]]

==See also==
* [[Media:Moodle2erd.png|ER Diagram of the installed system ]] - a single big png image
* [[Database_Schema]] - some automatically generated diagrams.
* [[Database FAQ]]
* [[XMLDB_Documentation]] - the XMLDB system used to define, edit, and update the database structure.
* [http://www.examulator.com/er Moodle 2.2 ER Diagram] by Marcus Green
* [http://www.examulator.com/er/er_howto.html Moodle ER Diagram HowTo]: Creating a Moodle ER Diagram by generating a log from the install process

[[Category:XMLDB]]