[drupal-devel] Request for input on Quiz module data structure
drupal-devel at webchick.net
Thu Oct 13 04:47:43 UTC 2005
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.
"I think you meant .png on that last link."
You are exactly right. :) New version of the ERD is at
"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:
...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!!
More information about the drupal-devel