Database design for a survey
Definitely option #2, also I think you might have an oversight in the current schema, you might want another table:
+-----------+
| tblSurvey |
|-----------|
| SurveyId |
+-----------+
+--------------+
| tblQuestion |
|--------------|
| QuestionID |
| SurveyID |
| QuestionType |
| Question |
+--------------+
+--------------+
| tblAnswer |
|--------------|
| AnswerID |
| QuestionID |
| Answer |
+--------------+
+------------------+
| tblUsersAnswer |
|------------------|
| UserAnswerID |
| AnswerID |
| UserID |
| Response |
+------------------+
+-----------+
| tblUser |
|-----------|
| UserID |
| UserName |
+-----------+
Each question is going to probably have a set number of answers which the user can select from, then the actual responses are going to be tracked in another table.
Databases are designed to store a lot of data, and most scale very well. There is no real need to user a lesser normal form simply to save on space anymore.
My design is shown below.
The latest create script is at https://gist.github.com/durrantm/1e618164fd4acf91e372
The script and the mysql workbench.mwb file are also available at
https://github.com/durrantm/survey
I think that your model #2 is fine, however you can take a look at the more complex model which stores questions and pre-made answers (offered answers) and allows them to be re-used in different surveys.
- One survey can have many questions; one question can be (re)used in many surveys.
- One (pre-made) answer can be offered for many questions. One question can have many answers offered. A question can have different answers offered in different surveys. An answer can be offered to different questions in different surveys. There is a default "Other" answer, if a person chooses other, her answer is recorded into Answer.OtherText.
- One person can participate in many surveys, one person can answer specific question in a survey only once.
As a general rule, modifying schema based on something that a user could change (such as adding a question to a survey) should be considered fairly smelly. There's cases where it can be appropriate, particularly when dealing with large amounts of data, but know what you're getting into before you dive in. Having just a "responses" table for each survey means that adding or removing questions is potentially very costly, and it's very difficult to do analytics in a question-agnostic way.
I think your second approach is best, but if you're certain you're going to have a lot of scale concerns, one thing that has worked for me in the past is a hybrid approach:
- Create detailed response tables to store per-question responses as you've described in 2. This data would generally not be directly queried from your application, but would be used for generating summary data for reporting tables. You'd probably also want to implement some form of archiving or expunging for this data.
- Also create the responses table from 1 if necessary. This can be used whenever users want to see a simple table for results.
- For any analytics that need to be done for reporting purposes, schedule jobs to create additional summary data based on the data from 1.
This is absolutely a lot more work to implement, so I really wouldn't advise this unless you know for certain that this table is going to run into massive scale concerns.