Solved

Database Schema for Open Ended Questionnaire

Posted on 2013-05-17
6
609 Views
Last Modified: 2013-07-05
I want to create an open ended questionnaire feature on my Website.  Admins manage their own site and they can create open ended questions (like a survey) that include things like menus, radio buttons, input, textareas and checkboxes. (Standard stuff).

I've written the admin side so that these are created on the fly based on the admin set up.  So different sites can have different quantities of answers.  I'm now looking at the best ways of storing the user answers.  

So for each user, I need to record:

1) Their user ID
2) The questionnaire ID (which questionnaire they are on)
3) The answers to the questions (varying number of questions).

I see two ways of doing this:

1) A set up where each user has ONE record with three fields.
 - userID
 - questionnaireID
 - user response

The user response field is a text field that contains a serialized string with their answers.  

2) The second way is to set up a database where the schema is that there will be as many records for each user as there are questions.  So now we add a fourth field.

 - userID
 - questionnaire ID
 - question ID
 - single answer response

The advantage I see to the first method is a more compact database (i.e. less records).

The advantage to the second is that you can pull individual data for a particular question without having to un-serialized the whole record.

My question is this.  Is the efficiency gained by the first option any consideration?  Or does it make more sense to just use the second option even though it greatly increases the number of records in the database (e.g. If you have a questionnaire with 12 questions and 100 people answer it, option one means 100 records but option two goes right to 1200).

Any insight offered would be helpful.  Thanks.
0
Comment
Question by:pkonstan1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 125 total points
ID: 39175494
I dont think even option 1 will save you a lot of database size, as when you serialize the  answers in a XML format (assuming it will be in XML), the one row will take almost same space as of the option 2.
So, if you are thinking of going to option 2 because of database size, think once again. it might not drastically reduce the size.

Verdict:
It really depends on the way your UI behaves, if your UI wants to get all the answers once from the DB and also sends all the answers of the questionaire once to the DB, then It is better to use the option 1 approach, as this will have less DB traffic and also will improve the performance.

if you are planning to retrive answer by answer for an user from UI, then option 2 will be more useful than option 1 as  you need not de-serialize the xml to object and find the question id in it.

But most of the questionaire sites will work, as I described in the point 1 above, so it would be good to go with option 1 not because of compact DB size, but becuase it will boost your performance and decreases the DB reads.

I hope this helps.
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 25 total points
ID: 39175724
Also keep in mind that at some point you might want to develop some metrics on individual questions (%correct, etc.) and this would be much easier to do with option 2.
0
 
LVL 20

Assisted Solution

by:Mark Brady
Mark Brady earned 25 total points
ID: 39176049
In the case of adding extra metrics it is still do-able with option 1. I would create an associate array and store each question/answer etc in there then json_encode it all and make one write to the database.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 25 total points
ID: 39176081
Option 1 could be a maintenance nightmare.

As a questionnaire (q'aire for short) evolves over time, does each version of the q'aire get a different ID?  Or do you just add a revision#?


I would tend to use Option 2 and lock the question IDs: a given question and range of answers set is always a given q ID, that never changes.  Iow, the Q ids in the master q table are NOT serial based on the q'aire they are in, but are essentially independent entities that can appear in any q'aire.

Of course a given q'aire may assign a serial # -- q#1, q#2 -- to each master q id for that specific q'aire, but that is just for the sake of the q'aire, and does not change that qs core id in its master table.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39176085
@elvin66, I believe, you want to caliculate the metrics at the application layer, where as @BriCrowe can do it at the database layer. Now which method is good or bad depends on which server (application server / database server) will be more free at that time plus the amount of memory consumption plus the performance (as per performance I think DB will outway application as in case of applcaiton, we have to retrive the whole data again into memory through network, this step is not required for DB case).
0
 

Author Comment

by:pkonstan1
ID: 39177176
The way I avoid the "nightmare" is each question is given a unique ID (I call screen) and then a position indicator.  So if you need to insert a new question, it's ID may be out of sequence with position, but it's relationship is still maintained.  

On the topic of how most calculations are performed it is the first option where you read DB once to fill UI, then user manipulates data and it is saved back to the DB.

On the admin side to view the results, it follows a similar pattern.  The data is read from the DB for each entry and put into a table.  Then at the table label the information can be sorted and viewed.  If a change is made the entire record is put back in at once.

For most of these questionnaires it is rare that you'll need to do any type of mass manipulation of all records at once (e.g. Change everybody's blue answer to red).  And any queries that you do will be done in the table where you're viewing the data, not as a DB select.

Thanks for the input.  I'll leave open for a bit to allow any additional comments and then award points.
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question