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

[Robert Douglass]

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 
> data.

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.
> http://webchick.net/soc/quiz.module/newschema.sql
> 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 
differently, right?

> - 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. 
Good work!

-Robert