Development:Quiz database structure

Template:Quiz developer docsThe 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 the tables holding the teacher-supplied data, defining quizzes and questions, and the tables storing all the data that is generated when students interact with the quizzes and questions.

A further simplification is possible by separating out the questiontype specific tables. They are logical extensions to other tables and therefore are not necessary for understanding the general basic model. They are therefore explained on the developer documentation page for the individual question types.

The diagram below shows how the most important tables are linked to one another.

Teacher-supplied data

quiz

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.

The only field that requires additional information is the optionsflag, which... I will put an explanation here eventually --Gustav Delius 06:02, 5 February 2006 (WST)

For quizzes that contain random questions the $quiz object may acquire an additional property

questionsinuse: A comma separated list of questions that are being used in the quiz. This is used by the random questiontype to avoid choosing a question that is already being used.

quiz_questions

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.

id: int(10) NOT NULL auto_increment,; Primary key. This 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.

category: int(10) NOT NULL default '0',; Foreign key: refers to an id in the table quiz_categories

parent: int(10) unsigned NOT NULL default '0',; Foreign key: refers to an id in the table quiz_questions. This field is set to zero except in the case of wrapped questions as they are used for example in 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). If parent is not '0' the question is never shown in a list of questions, because it is not a valid question by itself. This is also used for hiding random questions from the question list. Their parent field is simply set to their own id.

name: varchar(255) NOT NULL default ,; Question name that is used in question lists on the quiz editing pages

questiontext: text NOT NULL,; Main text of the question

questiontextformat: tinyint(2) NOT NULL default '0',

image: varchar(255) NOT NULL default ,

defaultgrade: int(10) unsigned NOT NULL default '1',; Default grade for a question, usually '1'. In the wrapped parts of cloze questions defaultgrade represents the weight of the part.

penalty: float NOT NULL default '0.1',; The penalty that is applied (if the respective quiz option is set) for an incorrect attempt at the question.

qtype: smallint(6) NOT NULL default '0',; The questiontype of the question. (Constants are defined in locallib.php)

length: int(10) unsigned NOT NULL default '1',; 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.

stamp: varchar(255) NOT NULL default ,; Unique identifier

version: int(10) NOT NULL default '1',; A number representing how often the question was edited.

hidden: int(1) unsigned NOT NULL default '0',; Controls whether to display a question in the question bank list in edit.php. Hiding questions is a mechanism to "delete" questions without removing them from the database and thus to allow them to be restored or "unhidden" 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.

In addition to these static fields, which are part of the generic question definitions, there are some additional fields that are only added to the object at runtime. There are two different kinds of fields: On the one hand there are questiontype specific fields, which are also part of the static question definition, but only apply to some questions. On the other hand there are fields that refer to the question instance, i.e. the question in the context of a particular quiz. The values for these later fields can differ when a question is used in different quizzes.

The runtime fields are added to question objects with the function quiz_get_questions_options() is called. This in turn calls the questiontype specific get_question_options() method for to add the options field.

maxgrade: This is the maximum grade that the teacher has assigned to this question in the context of the current quiz. This is by default equal to $questions->defaultgrade but the teacher can change this when editing the quiz. In the database it is stored in the quiz_question_instances table.

instance: Foreign key: refers to an id in the table quiz_question_instances

options: Optional field. It provides a namespace for any questiontype specific information that may be stored in this field. It is generally set by the get_question_options method.

name_prefix: The prefix to be used on all interactions printed as part of the question.

We now deal in alphabetical order with the remaining tables for the teacher-provided data.

quiz_answers

This table allows a common way to store one or more teacher-defined 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 teacher-defined answers in an entirely different way, or even to do away with teacher-defined answers and use some other method to mark the student-supplied answer. For example it could calculate the correct answer on the fly.

id: int(10) unsigned NOT NULL auto_increment,; Primary key

question: int(10) unsigned NOT NULL default '0',; Foreign key to quiz_questions table.

answer: text NOT NULL,; The string that represents the teacher-defined answer that will be compared to the student responses for grading and feedback purposes. The format of this field is entirely up to the question type.

fraction: varchar(10) NOT NULL default '0.0',; The fraction of the total mark that the student should earn for giving this particular answer. This could be a negative number for wrong answers. Note that it is stored in a varchar(10) field.

feedback: text NOT NULL,; Text that can be displayed to student as feedback when the student's responses match this particular answer.

In the past there was also a sequence number field was sometimes used to store the order of the different answers and was set to '0' if the order was of no importance.

quiz_categories

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.

id: int(10) unsigned NOT NULL auto_increment,; Primary key

course: int(10) unsigned NOT NULL default '0',; Foreign key to the course table

name

varchar(255) NOT NULL default ,

info: text NOT NULL,

publish: tinyint(4) NOT NULL default '0',

stamp: varchar(255) NOT NULL default ,

parent int(10): unsigned NOT NULL default '0',

sortorder int(10): unsigned NOT NULL default '999',

quiz_question_instances

Questions can be assigned different grades in different quizzes. These are stored in this 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.

id: int(10) unsigned NOT NULL auto_increment,; Primary key

quiz: int(10) unsigned NOT NULL default '0',; Foreign key to the id of the quiz table.

question: int(10) unsigned NOT NULL default '0',; Foreign key to the id of the quiz_questions table.

grade: smallint(6) NOT NULL default '0',; The maximum grade assigned to this question in this quiz. This grade was set by the teacher on the quiz editing page. At runtime this information is stored in the $question->maxgrade field.

quiz_question_versions

This feature is not finished and disabled. The table structure may still change. See Quiz developer docs#Question versioning for a discussion.

id: int(10) unsigned NOT NULL auto_increment,

quiz: int(10) unsigned NOT NULL default '0',

oldquestion: int(10) unsigned NOT NULL default '0',

newquestion: int(10) unsigned NOT NULL default '0',

userid: int(10) unsigned NOT NULL default '0',

timestamp: int(10) unsigned NOT NULL default '0',

Student-created data

quiz_attempts

In the quiz_attempts table a record is created each time a user starts an attempt at a quiz. This is one of the important tables and the corresponding $attempt object is passed between many of the quiz module functions and methods.

quiz: int(10) unsigned NOT NULL default '0',
The id of the quiz that is attempted
userid: int(10) unsigned NOT NULL default '0',
The id of the user who is attempting the quiz
attempt: smallint(6) NOT NULL default '0',
It is possible for a user to attempt a quiz several times, therefore the number of the attempt is stored in this field.
sumgrades: varchar(10) NOT NULL default '0.0',
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.
timestart: int(10) unsigned NOT NULL default '0',
The timestart field is set to the current time when an attempt is started and is never changed afterwards.
timefinish: int(10) unsigned NOT NULL default '0',
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.
timemodified: int(10) unsigned NOT NULL default '0',
This should be changed each time data in the record is modified.
layout: text NOT NULL,
a comma separated list of question ids, with a "0" denoting a page break. Usually the comma separated list ends with ",0".
preview: tinyint(3) unsigned NOT NULL default '0',
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.

quiz_states

States are saved for each interaction with a question. This allows a review of the complete history of a user's interactions with individual questions. The current state is the most important object used by the quiz module runtime code. This $state runtime object has a few additional fields not in the database that will be explained further down. The database fields are:

id: int(10) unsigned NOT NULL auto_increment,; Primary key

attempt: int(10) unsigned NOT NULL default '0',; Foreign key: refers to an id in the table quiz_attempts

question: int(10) unsigned NOT NULL default '0',; Foreign key: refers to an id in the table quiz_questions. The attempt id and the question id together sufficiently identify a question instance that a state belongs to.

originalquestion: int(10) unsigned NOT NULL default '0',; Foreign key: refers to an id in the table quiz_questions. It identifies which question was in use when the student attempted the question. This is part of the question versioning code.

seq_number: int(6) unsigned NOT NULL default '0',; A counter that provides information about the sequence in which the states were created.

answer: text NOT NULL,; This field is deleted during runtime and replaced with the responses field. For legacy reasons it is still called answer in the database. Questiontypes can store students' responses (usually in a serialized format) in this field using the method save_session_and_responses. Questiontypes are allowed to deviate from this and handle their response storage in any desired way. Some questiontypes do not use this field but instead store the student response in their own table extending this table.

timestamp: int(10) unsigned NOT NULL default '0',; A timestamp to record when the state was created. This could mainly be useful for audit purposes.

event

int(4) unsigned NOT NULL default '0',

Records the event or interaction type that lead from the previouse state to the current one. The field can take the value of any of the following constants (defined in locallib.php):

EVENTOPEN: The attempt has just been opened and this is the initial state of a question, i.e. the user has seen the question did not interact with it yet.
EVENTSAVE: The responses are just being saved, either because the student requested this explicitly or because the student navigated to another quiz page.
EVENTVALIDATE: The student requested a validation of the responses. (This is not supported by all questiontypes.)
EVENTGRADE: The responses are being graded but the question session is not closed. This is generally the case for adaptive questions.
EVENTCLOSE: The responses are being graded and the question session is closed. Usually this happens because the whole attempt closes, either because the student requests it or because the time is up or because we are beyond the due date.
EVENTDUPLICATEGRADE: This is a strange one. It indicates that the responses would have been graded had they not been found to be identical to previous responses.

grade: varchar(10) NOT NULL default '0.0',; Calculated from the raw grade by deducting the penalty and rescaling to the defaultgrade value of the question. Note that this is a varchar(10) field like most grade-related fields in the quiz module.

raw_grade: varchar(10) NOT NULL default ,; The grade that was achieved for the question scaled to the question's weight or grade as assigned in the quiz_question_instances table

penalty: varchar(10) NOT NULL default '0.0',; The penalty incurred during the transition from the previous state to this one. This is different to the cumulative penalty, which can be calculated by adding up all the penalties from previous marking events.

In addition to the database fields a few fields are added to the states at runtime. They are dealt with by the questiontypes with the methods create_session_and_responses, restore_session_and_responses and save_session_and_responses and are defined as follows.

sumpenalty: The cumulative penalty, which can be calculated by adding up all the penalties from previous marking events. For efficiency these cumulative penalties are stored in the table quiz_newest_states so that they do not need to be re-calculated each time.

changed: This records whether a change has taken place to the runtime state. This is set to false when the state is restored from the database. Any code which changes the question state must set this field to true amd must increment seq_number. The quiz_save_question_session() function will save the new state object to the database if the field is set to true.

responses: This is automatically set to the value of the database field answer before that one is removed. The responses field contains an array of responses. In the default case of a single response the value can be found in ->responses[]. For questiontypes using several HTML form elements for their responses the array contains a value for each of the interaction elements. The indicies are determined by the name of the elements after the name_prefix is stripped.

last_graded: This field contains another state object representing the most recent graded state in the history of this question attempt. This is necessary, because if a state merely represents a save interaction, it should not affect the session in any way. Therefore another grade interaction has to proceed from the last graded state.

options: Optional field. Similarly to the options field in the question object, this field provides a namespace for any questiontype specific information. The difference is that the information in the state->options field should be state specific, whereas the question->options field should be static.

We now deal in alphabetical order with the remaining tables holding the student-created data.

quiz_grades

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.

id: int(10) unsigned NOT NULL auto_increment,; Primary key

quiz: int(10) unsigned NOT NULL default '0',; Foreign key refering to the id of the quiz table

userid: int(10) unsigned NOT NULL default '0',; Foreign key refering to the id of the user table

grade: double NOT NULL default '0',; The grade awarded for this quiz to this user

timemodified: int(10) unsigned NOT NULL default '0',; Unix timestamp to be updated each time the grade is changed

quiz_newest_states

This table exists only for efficiency reasons:

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.
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().

id: int(10) unsigned NOT NULL auto_increment,

Primary key

attemptid: int(10) unsigned NOT NULL default '0',

Foreign key refering to the id in the quiz_attempts table

questionid: int(10) unsigned NOT NULL default '0',

Foreign key refering to the id in the quiz_questions table

newest: int(10) unsigned NOT NULL default '0',

Foreign key refering to the id in the quiz_states table. This points to the newest state for this attempt and this question.

newgraded: int(10) unsigned NOT NULL default '0',

Foreign key refering to the id in the quiz_states table. This points to the newest state created by a grading event.

sumpenalty: varchar(10) NOT NULL default '0.0',

This stores the total penalty that this user has accumulated over previous graded responses to this question in this attempt.

Documentation