Quiz database structure

Revision as of 18:16, 4 February 2006 by Gustav Delius (talk | contribs) (Changed style of headings)

Jump to: navigation, search

The quiz data model has a fairly large pool of database tables, so the first step in explaining them is to provide some order. Conceptually it is possible to distinguish between a static model, which allows defining quizzes and questions, and a runtime model, which stores all the data that is generated when users interact with the statically defined quizzes and questions. Modifying the data in the tables from the static model is only possible for users with teacher privileges (this constraint is not imposed by the database, it is merely an observation). The data in the tables of the runtime model is created when student users interact with quizzes. Therefore these tables usually contain significantly more data than the ones from the static model.

A further simplification is possible by ignoring the questiontype specific tables. They are logical extensions to other tables and therefore are not necessary for understanding the general basic model. However, some information is provided for each questiontype specific table, namely which questiontype it belongs to (although that should be clear from the name), which table it extends and what the additional data is needed for.

Using these two criteria the list below puts some order into the collection of tables.

  • Static model
    • quiz
    • quiz_questions
    • quiz_answers
    • quiz_categories
    • quiz_question_instances
    • quiz_question_versions
  • Runtime Model
    • quiz_attempts
    • quiz_states
    • quiz_grades
    • quiz_newest_states
  • Questiontype specific tables
    • quiz_calculated
    • quiz_dataset_definitions
    • quiz_dataset_items
    • quiz_match
    • quiz_match_sub
    • quiz_multianswers
    • quiz_multichoice
    • quiz_numerical_units
    • quiz_question_datasets
    • quiz_randomsamatch
    • quiz_rqp
    • quiz_rqp_states
    • quiz_rqp_types
    • quiz_shortanswer
    • quiz_truefalse
  • Redundant tables
    • quiz_attemptonlast_datasets

These simplifications reduce the number of "interesting" tables significantly, and still some of them are only necessary for understanding very specific aspects of the quiz module. The diagram below shows how the (selected) most important tables are linked to one another.

Quiz database tables.gif

Static model


The quiz table contains the definition for all the quizzes. Each quiz belongs to a course, reflected by the course id, has a name and a short descriptive text (intro), an opening and a closing time and several fields that store the settings of various quiz options, each of which is explained in the quiz help that is linked to from the quiz settings page. One field that may require additional information is the optionsflag, which... (maybe Gustav can add an explanation here?).

The quiz id is used extensively to identify records from various runtime tables, reflecting the fact (surprise) that quizzes are the main players in the quiz module.


This table constitutes the item or question bank, i.e. the repository of defined questions. The quiz_questions table defines the data that is common to questions of all types. It provides each question with a unique id which is used as a foreign key in many other tables. for example the quiz_answers table allows to define an arbitrary number of answers that are part of the question. And many questiontypes have their own tables that hold more information about the question.

Most fields are self explanatory, however, there are a few that require additional explanation: the parent field is a means to provide support for wrapped questions (e.g. the multianswer questiontype). When a question wraps around any number of subquestions the subquestions will have their parent id field set to the id of the main question, thus allowing the question to find all its sub-questions (or wrapped questions). A side effect is that any question with a parent id other than "0" is not shown in the list of questions that can be added to a quiz. This side effect is also used for effectively hiding random questions from the question list. Their parent field is simply set to their own id.

It may seem strange then to also have a field called hidden, but that serves a slightly different purpose. Hiding questions is first of all a mechanism to "delete" questions without removing them from the database and thus to restore or "unhide" them at a later stage. Also the (unfinished and disabled) versioning feature uses the hidden field to prevent older versions of a question from cluttering the user interface.

A question can also have a length. This defines how many question numbers are required for this question. It is generally set to "1", but the description questiontype, for example, sets it to "0", reflecting the fact that it doesn't have a question number. We now deal with the remaining tables from the static model in alphabetical order.


This table allows a common way to define one or more answers for each question. It is not mandatory for a questiontype to make use of this table however. A questiontype may choose to store it's answers in an entirely different way, or even to calculate the correct answer on the fly.

The question id links each answer to a question. The sequence number field may be used to store the order of the different answers, it can be set to "0" though if the order is of no importance. The answer field stores whatever constitutes an answer for the concerned questiontype (each questiontype can make whatever it wants off it's answers), the fraction field stores the assigned score for the question (range 0..1) and the feedback field allows defining some feedback, to be displayed when the student's answer agrees with the defined answer record.


Categories are provided as a way to organize questions. Each category has a name and a descriptive text (info) and the sortorder as metadata. Categories allow hierarchical nesting via the parent id and can be private or published, i.e. they can be made available to teachers in other courses.

Since categories are simply a means for organising questions they are not vital for understanding how the quiz module works.


Questions can have different grades assigned in different quizzes. These are stored in the quiz_question_instances table. While, after a small extension, this table could also fulfill the purpose of storing the order of the questions in a quiz, this is currently still done in the questions field in the quiz table.


This feature is not finished and disabled. The table structure may still change.

Runtime Model


In the quiz_attempts table a record is created each time when a user starts an attempt at a quiz. It is possible for a user to attempt a quiz several times, therefore the number of the attempt is stored in the attempt field. The sumgrade field records the (unscaled) grade for the attempt, i.e. if the grades assigned to the questions add up to 8, but the maximum grade for the quiz is set to 10, then the sumgrades field can contain 8 at maximum.

The timestart field is set to the current time when an attempt is started and is never changed afterwards. The timefinish field is set to "0" initially and to the current time when the attempt is closed. This is exploited at several places in the code to determine whether an attempt has been closed or not (i.e. closed = timefinish > 0). For all other modifications of an attempt record the timemodified field should be changed as well.

Finally, there are the layout and preview fields. The preview field is a flag that marks a teacher preview (i.e. an attempt by a user with teacher privileges) that may be automatically deleted when the quiz is previewed again, and which is not taken into account when viewing statistics. The layout field contains a comma separated list of question ids, with a "0" denoting a page break. Usually the comma separated list ends with ",0".


States are saved for each interaction with a question. This allows to review the complete history of a user's attempts on individual questions. The seq_number field stores the order of this history, the answer field stores a questiontype specific string unless the questiontype stores its answers differently. The event field stores an integer which can be one of the named constant defined in the file locallib.php denoting for example a saving or a grading interaction amongst others. For more details see eventtypes.html

Of further interest are the grade, raw_grade and penalty fields. The raw_grade field stores the grade that was achieved for the question scaled to the question's weight or grade as assigned in the quiz_question_instances table. The grade field stores the actual achieved grade after deduction of the penalty. And in the penalty field the penalty for that state is saved. This is different from the cumulative penalty, which is stored in the quiz_newest_states table.

The originalquestion field is a construct that will be used by the versioning code. The question ids in the states will be changed to the ids of the new versions of the questions and the id of the question, which was used for the actual attempt, will be stored in the originalquestion field. We now deal with the remaining tables from the runtime model in alphabetical order.


The quiz_grades table merely stores a student's awarded grade for a quiz. Since it is possible to allow several attempts on a quiz, the grade stored is calculated depending on the quiz setting grademethod. This table exists mainly for convenience, because the values of its fields can be recalculated.


This table exists only for efficiency reasons:

  1. Via its 'newest' and 'newgraded' fields it gives attempt.php a way to quickly find the newest state and the newest graded state for an attempt. It allows the construction of SQL to select all the states that need to be loaded on attempt.php or review.php.
  2. Via its 'sumpenalty' field it gives quiz_apply_penalty() a quick way for getting at the accumulated penalty that needs to be applied. Without this field the penalties from all previous graded states would have to be added up each time. Not a big deal actually because this could be achieved with a single SQL query (using SUM) but this field was introduced when we still had the multiplicative penalty scheme around which would have been more difficult to recompute.

This table was introduced in Moodle 1.5 and is not populated for all states during the upgrade because on sites with a lot of existing states that could take too long. Rather it is done whenever needed by quiz_upgrade_states().

Questiontype specific tables


The quiz_calculated table is an extension to the quiz_questions table by the calculated questiontype. However, it would be more suitable to change that to be an extension of the quiz_answers table, which, from a data perspective, is already possible, since an answer id is stored in the answer field. The questiontype code would need some changes to take this into account, however.


The quiz_dataset_definitions table belongs to the abstract datasetdependent questiontype, which is currently only used by the calculated questiontype. It is an indirect extension to the quiz_questions table, because the quiz_question_datasets table can link a question to one or more datasets. Each dataset represents a variable, that is used either in the questiontext or in the answer to a dataset dependent question.


Dataset items can be created for each dataset. The quiz_dataset_items table stores these possible values for the variables defined in the quiz_dataset_definitions table.


The quiz_match table belongs to the match questiontype and extends the quiz_questions table. It is only used in the code for saving matching questions and can therefore be considered redundant.


The quiz_match_sub table belongs to the match questiontype and extends the quiz_questions table. It stores the pairs of questions and answers (as strings) that need to be matched for a correct solution.


The quiz_multianswers table belongs to the multianswer questiontype and is an extension of the quiz_questions table. It merely stores a comma separated list of question ids in the sequence field, which is important, because that's the only way to know which sub question belongs to which position in the questiontext.


The quiz_multichoice table belongs to the multichoice questiontype and is an extension of the quiz_questions table. The layout field does not seem to be used, the answers field stores the order of the answers (should be superseded by the seq_number field in the quiz_answers table) and the single field is a flag signaling, whether only one option or multiple options can be chosen.


The quiz_numerical table belongs to the numerical questiontype and is an extension of the quiz_answers table, defining a tolerance value for each answer.


The quiz_numerical_units table is used by the numerical questiontype and the calculated questionype. It extends the quiz_questions table, defining an arbitrary number of units that can be used in the responses.


The quiz_question_datasets table is used by dataset dependent questionypes (i.e. calculated) to link datasets to questions.


This extension to the quiz_questions table simply stores how many shortanswer questions should be randomly chosen to build this randomsamatch question.


No information.


No information.


No information.


The quiz_shortanswer table belongs to the shortanswer questiontype and is an extension of the quiz_questions table. The answers field stores a comma separated list of answer ids, which is redundant. The only valuable piece of information contained in this table is the usecase field, which is used to decide whether to do a case sensitive or case insensitive comparison for grading. quiz_truefalse

An extension of the quiz_questions table the quiz_truefalse table stores the answer ids for the true and for the false answers.