Database

Database structures

To help you create tables that meet these guidelines, we recommend you use the built in database definition (XMLDB) editor.

There are rules for how what numbers to use in version.php files in Moodle core.

1. Every table must have an auto-incrementing id field (INT10) as primary key. (see IdColumnReasons)
2. The main table containing instances of each module must have the same name as the module (eg widget) and contain the following minimum fields:
   • id - as described above
   • course - the id of the course that each instance belongs to
   • name - the full name of each instance of the module
3. Other tables associated with a module that contain information about 'things' should be named widget_things (note the plural).
4. Core tables in general should have single word names non-pluralised, and double word names pluralised only for the last word e.g. 'course', 'course_categories'. The only exceptions should be for reserved words e.g. 'files'. Some tables don't fit this pattern right now for historical reasons, but this will eventually be changed.
5. Table and column names should avoid using reserved words in any database. Please check them before creation. Table names may be up to 53 characters long (only 28 before Moodle 4.3), and Column names up to 63 characters long (only 30 before Moodle 4.3).
6. Column names should be always lowercase, simple and short, following the same rules as for variable names.
7. Where possible, columns that contain a reference to the id field of another table (eg widget) should be called widgetid. (Note that this convention is newish and not followed in some older tables)
8. Boolean fields should be implemented as small integer fields (eg INT4) containing 0 or 1, to allow for later expansion of values if necessary.
9. Most tables should have a timemodified field (INT10) which is updated with a current timestamp obtained with the PHP time() function.
10. Always define a default value for each field (and make it sensible)
11. Each table name should start with the database prefix ($CFG->prefix). In a lot of cases, this is taken care of for you automatically. Also, under Postgres, the name of every index must start with the prefix too. Note that, since Moodle 4.3 the max allowed prefix length is 10 characters.
12. In order to guarantee cross-db compatibility follow these simple rules about the use of the AS keyword (only if you need table/column aliases, of course):
    • Don't use the AS keyword for table aliases.
    • Don't use table aliases at all for DELETE statments (Mysql doesn't like it).
    • Do use the AS keyword for column aliases.
13. Never create UNIQUE KEYs (constraints) at all. Instead use UNIQUE INDEXes. In the future, if we decide to add referential integrity to Moodle and we need UNIQUE KEYs they will be used, but not now. Please note that the XMLDB editor allows you to specify both XMLDB-only UNIQUE and FOREIGN constraints (and that's good, in order to have the XML well defined) but only underlying INDEXes will be generated.
14. Those XMLDB-only UNIQUE KEYs (read previous point) only must be defined if such field/fields are going to be the target for some (XMLDB-only too) FOREIGN KEY. Else, create them as simple UNIQUE INDEXes.
15. Tables associated with one block must follow this convention with their names: $CFG->prefix + "block_" + name_of_the_block + anything_else. For example, assuming that $CFG->prefix is 'mdl_', all the tables for the block "rss_client" must start by 'mdl_block_rss_client' (being possible to add more words at the end, i.e. 'mdl_block_rss_client_anothertable'...). This rule will be 100% enforced with Moodle 2.0, giving time to developers until then. See Task 6786 for more info about this.
16. Never make database changes in the STABLE branches. If we did, then users upgrading from one stable version to the next would have duplicate changes occurring, which may cause serious errors.
17. When refering to integer variable in SQL queries, do not surround the value in quotes. For example, get_records_select('question', "category=$catid") is right. get_records_select('question', "category='$catid'") is wrong. It hides bugs where $catid is undefined. (This thread explains.)
18. Never use double quotes for variable values in SQL queries (e.g. 'SELECT * FROM {user} WHERE username = "someuser"'). While this is OK for MySQL, which does not respect ANSI standard for databases, Postgresql is treating double quoted variable this as system identifier (e.g. field name).
19. Moodle does not support database "views", don't use them. See Petr's comment on Task 25407 for more info about this.