It's in a bit of a mess (just File > Save As'ed web pages), but attached are the original design specifications for the quiz module from Robert Douglass's old site, for historical purposes and also to provide an overview of what we had in mind when building this.

Files: 
CommentFileSizeAuthor
#1 quiz schema.zip78.46 KBwebchick
Project Specifications.zip452.87 KBwebchick

Comments

webchick’s picture

FileSize
78.46 KB

Additionally, here are some thoughts I wrote up on the developer list about changes to the data schema which could help make this more scalable/flexible. Referenced files are attached.

----

Hi, folks. Hope that this is the right place for this. Apologies in advance for the length, but I wanted to make sure everyone's on the same page before asking for feedback.

== BACKGROUND ==
As most know, I worked over the summer on the Quiz module. Thanks to a generous injection of funding from Ejovi Nuwere at SecurityLab.net, I've been able to both complete most of the work that was not done by the other student assigned to this project during SoC, and am also able to now focus on adding additional features to the module.

The quiz module was developed around Robert Douglass's specification (which I've temporarily mirrored here: http://drupaldev.snarkles.net/node/2). In a nutshell, this means rather than creating a simple module that could do multiple choice questions, instead creating a quiz *framework* which could potentially support almost limitless question types, from traditional multiple choice/fill in the blank, to really innovative ideas such as "reading" a question aloud to gage reading comprehension and so on.

== HOW IT CURRENTLY WORKS ==
The current schema can be viewed here: http://cvs.drupal.org/viewcvs/drupal/contributions/modules/quiz/quiz.mys...
For the more visually inclined, I've uploaded an E-R Diagram here: http://www.webchick.net/soc/quiz.module/quiz-schema-old.png

This is based off the original proposal, where serialized arrays (/me ducks in advance) are used to store answer, feedback and user response data.

NOTE: Skip to "THE PROBLEM" if you don't care about how this stuff is stored currently :P

A typical result array (stored in quiz_results.results) might look like:

Array
(
[10] => Array
(
[input] => Array
(
[0] => 0
[1] => 2
[2] => 3
)

[feedback] => Array
(
[0] => Correct!
[1] => Correct!
[2] => Correct!
)

[score] => 1
)

[6] => Array
(
[input] => Array
(
[0] => 2
)

[feedback] => Array
(
[0] => Nope, fooled you!
)

[score] => 0
)
...
)

The array keys (10 and 6) are question node IDs... in other words, this is how the user responded to both question 10 and question 6. Over the course of the quiz, this array keeps growing, with one array of results per question.

"input" is an array of the answer(s) that the user selected on that question. The first question was a multiple choice question, so the user plugged in three answers (answers 1, 3, and 4 -- remember that arrays start at 0 index). The second question was only a single-choice question, and the user selected answer 3.

"feedback" is an array of the feedback to the answer(s) the user selected for input. As you can see, the user answered correctly on all three answers on the first question, but did not answer correctly on the second question.

Finally, "score" is either a 1 (indicating the answer was correct) or a 0 (indicating the answer was not correct).

== THE PROBLEM ==

The question was raised to me:

"If there's a million questions, and a
million students, and we want to find out for a given
question, how many student got it right how do we do that?"

Right now, the answer is something along the lines of this:

-----------------------------------------------------------------------

function get_question_results($quiz_nid, $question_nid) {
// Array to store question results
$question_results = array(
'right' => 0,
'wrong' => 0
);

// Grab a list of the results array for each quiz attempt
$quiz_results = array();
$results = db_query("SELECT r.results FROM {quiz_results} r WHERE r.quiz_nid = %d", $quiz_nid);
while ($result = db_fetch_object($results)) {
$quiz_results[] = unserialize($result->results);
}

// Now, loop through each result and look for the given question
foreach ($quiz_results as $key => $value) {
if ($value[$question_nid]) {

// If question is found check if it was answered correctly or not
if ($value[$question_nid]['score'] == 1) {
$question_results['right']++;
} else {
$question_results['wrong']++;
}
}
}
return $question_results;
}

// Call the function and output results
$results = get_question_results(12, 10);
echo $results['right'] ." students answered this question right, " . $results['wrong'] ." answered it wrong.";
-----------------------------------------------------------------------

This is quite processor and RAM-intensive, since it has to load ALL of the results for each attempt on a given quiz first, then parse through those to find individual question attempts, and then finally retrieve the answers from each and evaluate whether or not they were correct.

And this same basic logic has to be completed for each potential query someone might want to throw at it.. Such as, "What is the average score of this user among all exams?"

In short, it doesn't look like this solution is going to be very scalable in the long-term for the kind of ad-hoc querying that educators are likely going to really want to be able to do.

== THE SOLUTION? ==
I've created an alternate, normalized data schema and wanted to get your feedback on it, if you get a chance.
http://webchick.net/soc/quiz.module/newschema.sql
E-R Diagram: http://webchick.net/soc/quiz.module/quiz-schema-new.sql

Changes:
- quiz/quiz_questions tables stay the same

- multichoice turns into 'quiz_question' containing only a primary key and a "properties" field to take the place of "multiple_answers." I envision this field as being more flexible than multiple_answers, as it could potentially store a plethora of options for very diverse array question types, perhaps in the form of key/value pairs or something (multichoice=1;email_results=1; etc.)

- answers are now broken out into a 'quiz_question_answer' table, which links back to quiz_question with separate fields for answer, feedback, and points. The answer field itself could store a wide variety of options, such as "Answer" for a multichoice question, "St. Paul|Saint Paul" for a fill-in-the-blank question, and so on. Since answer display is handled in each question type module in the _form and _render_question hooks, we can still have the advantage of storing data independently from the actual quiz module. Feedback, too, can be stored in whatever way makes sense for the answer, and points can indicate whether a question is incorrect (0 points) or can also be used for 'weighting' questions (answer 1 is 20 points, answer 2 is 50 points, etc.)

- Results are now stored in two different tables: quiz_result for the overall properties (start time/end time, etc. -- essentially the same as the current table) and quiz_question_results which provides the textual representation of what that user put for his/her answer on each question attempt. This could be stored in a similar manner to the answers table... for multiple answer questions, it could be 23,63,3 to indicate the answer ids selected, for fill-in-the-blank it could have 'Minneapolis', etc. Each question type's _evaluate_question hook would take the data from the answer field and check it against the given question's answers.

- Fields representing times and dates are switched to DATETIME instead of INTEGER (for UNIX timestamp) in order to help with date range calculation

And if you made it through that, a hearty congratulations. :P

Any thoughts?

nicholasThompson’s picture

This looks very sound to me, I'd just like to correct one of the links. The new E-R Diagram is:
http://webchick.net/soc/quiz.module/quiz-schema-new.png
not
http://webchick.net/soc/quiz.module/quiz-schema-new.sql

I'll post back later with any progress I make on this today.

gilcot’s picture

Very good thougth. However :

Fields representing times and dates are switched to DATETIME instead of INTEGER (for UNIX timestamp) in order to help with date range calculation

Sorry, but Drupal uses INTEGER because it offers more portability over databases.
In another hand, i cannot see how DATETIME is better in date range computation.

But, sincere congratulations.

webchick’s picture

Maybe not "better," but certainly "easier" because you can use all the SQL functions like DATE_SUB and stuff on them.

gilcot’s picture

Even if not mentionned @ http://drupal.org/node/1395 one should avoid specific database function because it's the best way to down portability. Off course, ANSI functions are exeptions.. but most of features @ http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html are not ANSI compatible :(

Senpai’s picture

There's now a first draft page in the Handbooks for Quiz module documentation. http://drupal.org/node/195898

westwesterson’s picture

Page looks great, thanks!

Senpai’s picture

Assigned: Unassigned » Senpai
Status: Active » Fixed

I've got this handled and 89% complete. Expect a mini-epic in the Handbooks by this weekend.
----
Senpai
achieveinternet.com

westwesterson’s picture

The handbook is looking good. There are some things that might need re-going over. That to-do list is painfully old. I've contemplated retrofitting it for a while. A more up-to-date list of feature requests and plans for the future can be found in the quiz group on groups.drupal.com.

Check out the quiz help page, within the module, this describes some of the most important functionality. That reminds me, I want to tie the module in more with the help system so that users can view the help page to help them figure out what options do. (I will create a new issue for this)

as of yet documentation doesn't mention views this is another huge step for the quiz module

If you need help with the differences between quiz 1.0 and 2.0 I've been updating the changelog with new functionality added since quiz 1.1. This should make that part easy.

Here are some concerns users might have.
- why can't i change the access to my views enabled admin quiz page to have more people access it.
- permissions for views are handled by views not quiz, users need to have views ui enabled and override the view, then they can choose which roles can see the view.

If i think of more important things that are missing, I'll let you know, but those are some of the important things i can think of.

Anonymous’s picture

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

rajeshrjsh’s picture

i need quiz module 7 ER diagram, and schema.