Storing survey data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Storing survey data

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

each question has a record, each answer gets a record with a link to the user that submitted it (unless "anonymous")
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

So, you saying i should have a setup like this!?

-------------------------
Table Questions

ID
Question
-------------------------


-------------------------
Table Answers

QID
Answer
UserID
-------------------------
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yes sir!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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"?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. :)
Post Reply