[drupal-devel] Request for input on Quiz module data structure [long]

[Angie Byron]

Wow!! Thanks very much for all of the input so far!

To address a couple things:

"number_of_questions" can be queried and therefore should not be
stored IMO if you're going for full normalization."

Not quite. The reason for number_of_questions is to put a cap on the number of 
questions actually shown in the quiz. Let's say I have a question bank of 200 
questions. 40 of those are about topic X, but I only want a 25 question quiz 
about topic X, not a 40 question one. I want questions #5, #14, and #27 to show 
up in every single quiz, but the rest can just be random (this is determined by 
the question_status field in quiz_questions). That's what that field is for. If 
there's a better way to do this that I'm missing though, please feel free to 
indulge me. :)

"I think "takes" could be have a more descriptive name and should be
bigger than tinyint."

Takes is "number of takes a user is allowed to have on this quiz." Values range 
from 0 (Unlimited) to 10. I could change this to an integer field, but I can't 
envision a situation where someone would want > 255 takes allowed and wouldn't 
just call that "Unlimited." But if you have a use case for this, please by all 
means let me know! My guess is that you're right and the field indeed needs to 
be renamed so it is more clear that it's describing the allowed number of takes, 
and not a calculation of the number of takes that the quiz has actually had. 
(maybe allowed_takes?)

"I think you meant .png on that last link."

You are exactly right. :) New version of the ERD is at 
http://webchick.net/soc/quiz.module/quiz-schema-new.png, sorry.

"The rule of thumb is to start normalized, ideally with a 3NF, then
only de-normalize for a good reason (mainly performance)."

Indeed. I'm normally a "normalization nazi" (hehe ;)) but I was not familiar 
with Drupal coming into this project and thought maybe serialized arrays were 
"the Drupal way" of handling this type of scenario, since it was part of the spec.

"This leaves the DATETIME instead of a Unix timestamp issue. There must
have been a good reason for doing it this way back when Drupal
started.  Maybe it was interoperability between PostgreSQL and MySQL,
maybe it was something else."

Yeah, does anyone know why this is? I tried Googling "DATETIME vs. timestamp" 
and couldn't find anything in support of using timestamps (though admittedly, I 
didn't look very long). SQL is littered with date comparison and manipulation 
functions (where PHP has very few) so it seems to make the most sense to 
leverage this power on the database end.

"I am very interested in understanding the quiz module and what you are doing 
with it. Is the module you are working on the same one that is in CVS?"

Yes, it's currently in CVS at 
http://cvs.drupal.org/viewcvs/drupal/contributions/modules/quiz/ and you can 
download a daily tarball (updated daily) at 
http://drupal.org/files/projects/quiz-cvs.tar.gz. I haven't widely publicized 
this yet because since the data model is likely about to radically change, I 
don't want too many people locked into the "old, inflexible way" of doing 
things. But the basic functionality is there.

"It seems to me that you are making the problem harder than it needs
to be. I wonder why you are not using the power of SQL to do the
sorting, comparing and counting at the database side."

The problem is in the way the results are stored currently (as a serialized 
array). Here is an example of the contents of a "results" field in the database:

a:2:{i:10;a:3:{s:5:"input";a:3:{i:0;i:0;i:1;i:2;i:...  	

...which as you might guess is basically impossible to query properly (unless 
I'm missing something really obvious). The only way that I know of to get at 
this data is to call PHP's unserialize() function on the field to bring it into 
an array that can then be played with inside PHP.

I'm hoping that by changing the schema to the new way, I can indeed use a 
one-liner SQL query to retrieve any kind of ad-hoc questions someone might have 
about the data. That's why I wanted to run the idea past some folks who've been 
doing this a bit longer than I have, to make sure I'm not overlooking anything. :)

Thanks again so much!!

-Angie