Page 1 of 1
Storing survey data
Posted: Tue Sep 13, 2005 3:07 am
by JayBird
Hi,
Say i have an online survey that hold 100 questions.
Would it be efficient to have 100 fields to store the answers for each user, or is there a better way of storing this kind of data.
The data will later need to be analysed to gather statictics and such.
Thanks
Mark
Posted: Tue Sep 13, 2005 7:49 am
by feyd
each question has a record, each answer gets a record with a link to the user that submitted it (unless "anonymous")
Posted: Tue Sep 13, 2005 8:07 am
by JayBird
So, you saying i should have a setup like this!?
-------------------------
Table Questions
ID
Question
-------------------------
-------------------------
Table Answers
QID
Answer
UserID
-------------------------
Posted: Tue Sep 13, 2005 8:13 am
by feyd
yes sir!
Posted: Tue Sep 13, 2005 8:20 am
by JayBird
What data type would be bets for storing the answers, as the answer could be anything including dates, numbers, text, mutliline text etc etc.
Also, how would this system handle check boxes where multiple answers could be selected....insert a new answer record for each box that is checked!?
Another thing, as there are going to be lots of questions, user are going to be able to come back at a later date to continue the survey.
Should their temporary answer be stored in the answers table, but flagged as incomplete, or should i have a separate "tempAnswers" table...then on completion, move the answers from "tempAnswers" to "Answers"?
Posted: Tue Sep 13, 2005 8:40 am
by feyd
Pimptastic wrote:What data type would be bets for storing the answers, as the answer could be anything including dates, numbers, text, mutliline text etc etc.
I'm thinking a blob where you store an object form of the answer. This gives the application the ability to do further analysis and/or validation. I may store the "type" (object to use really) with the question, as the question knows what the answers could be.
Pimptastic wrote:Also, how would this system handle check boxes where multiple answers could be selected....insert a new answer record for each box that is checked!?
What I've done in the past is where a select box, check or radio group is needed, I used a secondary table containing the valid answers (since those are definite).
Pimptastic wrote:Another thing, as there are going to be lots of questions, user are going to be able to come back at a later date to continue the survey.
This path definitely can help with that.
Pimptastic wrote:Should their temporary answer be stored in the answers table, but flagged as incomplete, or should i have a separate "tempAnswers" table...then on completion, move the answers from "tempAnswers" to "Answers"?
Depending on your preferences I'd either use an "incomplete" flag or simply analyze their answers compared to questions (in a really cursory pass). The analysis version basically requires that all [required] questions be answered. (Questions could have a "required" flag to achieve this.) But that all depends on how the basic rules of this question and answer survey works.
