Marcus Green June 2017
These diagrams can help understand how Moodle stores data in tables and how those tables are related. For example a user may be enroled on multiple courses, so the user_enrolments table has a relationship from its userid column with the id column in the user table.
The main diagram gives an overview of all the standard Moodle sub-systems with table names. To get the maximum benefit I recommend downloading the *.mwb file and opening it with the MySQL Workbench tool (available freely under the GPL licence). This allows tables to be expanded and the names of all columns, comments and relationships to be seen. Note that for technical reasons some of the comments may be truncated to 60 characters. To see the full comments you can directly view the table structure from the moodle web interface via admin/tool/xmldb/ or you can view a web page with all the xml for the table creation here.
I have created a new function in the sqlgenerator tool that checks that the table names are correct in the morekeys.xml file. This identified some errors in table names and so this version of the diagram is more accurate.
Why are some relationships missing?
Some areas may simply be missing the primary/foreign key relationships. Moodle will not actually install if the existing relationships are enforced with a database engine that supports them such as InnoDB.It would make sense to encourage developers to move to a structure with relationships between tables even if only to help with documentation. This issue is partially documented here https://tracker.moodle.org/browse/MDL-49795
Why are some of the groupings wrong?
The groupings (e.g. Gradebook, Backup etc) and allocations of tables to them are a combination of my own understanding of the Moodle subsystems and the documentation to be found at http://docs.moodle.org/dev/Database_schema_introduction . If you think I have it wrong let me know.
Why are some modules missing?
The modules covered are anly those that get installed by default from the download basic Moodle installation. It would be possible to create diagrams for additional modules such as activities.
Why use MySQL Workbench
The original reason was mainly because it was free/libre. However having used many other diagramming tools over the years I would pick it again.
What licence is the diagram available under?
Ths diagrams, files and these pages are available under the GNU Public Licence or the Creative Commons License at http://creativecommons.org/licenses/by/4.0/ I have selected those licenses to give the maximum flexibility in use, re-use and modification of this content including for commmercial purposes without further reference to me.