Storing demographic responses, performing analysis, design & implementation

[roopurt18]

This topic is an overlap of MySQL and design so I'm placing it here, feel free to move it to the MySQL board if it's more appropriate there.  Following is a bit of setup to the problem, if you want to jump directly to the problem scroll down to the section labeled "The point of this thread."

 

The software I develop allows our clients to build a survey that captures vital and demographic data.  Vital data is that data of interest to all of our clients, items such as first and last names, address information, contact information, etc.  Demographic data are those types of questions that are multiple choice, open-ended responses, etc. that differ from client to client.  Since there's no way for me to predict which questions our clients would want to include on their surveys, I built an interface where they can create their own questions.

 

<Side note>  I have a beta environment set up where you can test this if you're curious.  The login credentials are beta1 / beta1; there is a top-left menu labeled InterestView.  The sub-menu Demographic Setup allows users to build their demographic questions; the sub-menu Preview allows a preview of the entire survey as it would appear to users.

URL:  http://www.ibswebview.com/wv/beta/admin </Side note>

 

iview_demo_setup

id int Demographic question ID

order int Display order of the question; i.e. 1 means first question, 2 means second question, etc.

setup text Serialized PHP array that contains the question setup; see note below.

 

<note> There are 5 basic question types that can be created; consistent to all of them are the prompt, type, extras and possibly a few more fields.  extras is itself an associative array whose members depends on the question's type.  For instance, extras in multiple choice questions includes details about the choices while in a rating scale question it includes details about the ratings to display.

I chose to store the questions' setup parameters as serialized arrays because it facilitates easier expansion of question types and it is unlikely I will have to run queries such as "Which questions are multiple choice and include 'Apples' as a choice?"  Which is to say I see a great deal of complexity in trying to represent a demographic question's setup with multiple DB tables and very, very little gain.

</note

 

iview_response

id int Response ID

entered datetime Timestamp of when response was received

exported tinyint 1 if exported to our server software, 0 otherwise

 

For each response submitted through the form, a master record is created in iview_response.  Currently, only the vital information (first name, last name, address, phone, e-mail, etc.) is being saved (elsewhere in the database).

 

The point of this thread:

I now need to store the answers to the demographic questions in the database and I will later need to perform statistical analysis on the responses.  My initial thoughts are the following table design:

 

iview_demo_short_ans - Used for short answers, i.e. multiple choice & rating scale

responseID int Which response record this answer belongs to

questionID int Which question this answer belongs to

answer varchar() The user's answer

 

iview_demo_long_ans - Used for long answers, i.e. open-ended text questions

responseID int Which response record this answer belongs to

questionID int which question this answer belongs to

answer text The user's answer

 

For multiple choice and rating scale questions, likely statistics would be frequency of choices or for people who answered question X as A, how did they answer question Y?

 

For the open-ended text responses, I'm thinking some sort of word frequency statistics.  For example, how many responses contained words associated with dissatisfaction, a positive experience, etc.

 

I'm curious if anyone else has dealt with this scenario, if you think I'm headed in the correct direction, or any alternative ideas that can be thought of.