[drupal-devel] Request for input on Quiz module data structure
rob at robshouse.net
Thu Oct 13 06:07:26 UTC 2005
Angie Byron wrote:
> This is based off the original proposal, where serialized arrays (/me
> ducks in advance) are used to store answer, feedback and user response
Rapid prototyping =)
> == THE SOLUTION? ==
> I've created an alternate, normalized data schema and wanted to get your
> feedback on it, if you get a chance.
> E-R Diagram: http://webchick.net/soc/quiz.module/quiz-schema-new.sql
E-R Diagram link not found.
> - 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.)
Not normalized, but probably ok for properties. Unless you get requests
to do queries such as "Show me all the mulitple choice questions where
the results get emailed".
> - 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.
Also not normalized. If you're going to have each question type do its
own logic for splitting the "Answer" field to get the multiple options,
it seems like you could just make special tables for that question type
that reflect its needs, and actually make normalized tables. I think the
whole point is that every question type needs to save the data
> - 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,
Once again, if you're going to do the work and get away from custom data
types that need extra processing, storing a comma separated list of ids
isn't what you want. Making a user_id|quiz_id|question_id|answer table
with the first three columns being the primary key would be the
normalized way. If you're going to store a list of answer ids in a
column, however, use serialize(). It's not that serialize is evil; it is
definitely better than making your own string parsing routines where you
have to remember whether to split on comma or pipe, and worry about
whether someone might use the pipe in the answer.
In all, these look like really good improvements on the original spec.
More information about the drupal-devel