Database Schema for Open Ended Questionnaire
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Surendra Nath
@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).
ASKER
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.
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.