Question database structure

This page documents the database tables use by the question bank and question engine.

The distinction between the question bank and the question engine is that the question bank stores the question definitions, organised into categories, while the question engine stores the information a user's attempts at a question or questions.

Overview

File:Question database.dia Dia file, should you wish to have a copy of this diagram in an editable format.

Common field types

Fields that hold a question score, like question.defaultgrade or question_answers.fraction, should be NUMBER(12,7).
Question text, feedback, etc. should be TEXT(short).

Detailed table descriptions

In Moodle 2.0 dev, you can get these by going to Administration -> Development -> XMLDB and clicking on the [Doc] link next in any of the relevant rows (mod/quiz/db, mod/quiz/report/xxx/db). Looking directly there is much more likely to be up-to-date than relying on information that has been copied here.

(Wouldn't it be nice if that documentation was automatically build and available online.)

Individual question types

The following core question types have their own database tables:

Following what happens when questions are attempted

When a user is attempting questions (for example by attempting a quiz), the following query gives the best view of what is happening:

SELECT
    st.id,
    st.attempt,
    st.question,
    st.seq_number,
    CASE WHEN st.id = sess.newest THEN 'newest' END AS isnewest,
    CASE WHEN st.id = sess.newgraded THEN 'newgraded' END AS isnewgraded,
    CASE st.event WHEN 0 THEN '0 (open)' WHEN 2 THEN '2 (save)' WHEN 3 THEN '3 (grade)'
        WHEN 5 THEN '5 (validate)' WHEN 6 THEN '6 (close&grade)' WHEN 7 THEN '7 (submit)'
        WHEN 8 THEN '8 (close)' WHEN 9 THEN '9 (manualgrade)' ELSE CAST(st.event AS varchar) END AS event,
    st.answer,
    st.raw_grade,
    st.grade,
    st.penalty,
    sess.sumpenalty,
    sess.manualcomment,
    st.timestamp

FROM mdl_question_states st
JOIN mdl_question_sessions sess ON st.question = sess.questionid AND st.attempt = sess.attemptid
JOIN mdl_quiz_attempts quiza ON quiza.uniqueid = sess.attemptid

WHERE quiza.id > 0

ORDER BY st.attempt, st.question, st.seq_number;

I believe that works in both Postgres and MySQL. You probably need to adjust the WHERE clause so that only returns the rows you are interested in. (Actually, does not quite work in MySQL. You need to change 'CAST(st.event AS varchar)' to 'st.event' in the middle.)

Watching the results of that query as you work through a quiz attempt with a test user account is a very good way to work out what is going on in the code and find bugs.

Rough change-log

Moodle 2.0

New field question_sessions.flagged.
All fields that store grades were reviewed and set to the recommended types mentioned above.

Moodle 1.9

New question bank:
- New fields question.timecreated, timemodified, createdby and modifiedby.
- question_category.course changed to question_category.contextid.
- Removed field question_category.published.

Moodle 1.7

question_sessions.comment renamed to manualcomment (reserved word).
new column question.generalfeedback.

TODO, check CVS history for more.

Documentation