调查的数据库设计 [英] Database design for a survey
问题描述
我需要创建一个调查，其中的答案存储在数据库中.我只是想知道在数据库中实现它的最佳方法是什么，特别是所需的表.该调查包含不同类型的问题.例如:评论文本字段、多项选择题，以及可能包含多个答案的问题(即勾选所有适用的选项).
I need to create a survey where answers are stored in a database. I'm just wondering what would be the best way to implement this in the database, specifically the tables required. The survey contains different types of questions. For example: text fields for comments, multiple choice questions, and possibly questions that could contain more than one answer (i.e. check all that apply).
我想出了两种可能的解决方案:
I've come up with two possible solutions:
创建一个巨大的表，其中包含每项调查的答案提交.每列将对应于来自民意调查.即 SurveyID、Answer1、答案2，答案3
Create a giant table which contains the answers for each survey submission. Each column would correspond to an answer from the survey. i.e. SurveyID, Answer1, Answer2, Answer3
我认为这不是最好的方法因为有很多问题在这次调查中，似乎不太如果调查要更改，请灵活处理.
I don't think this is the best way since there are a lot of questions in this survey and doesn't seem very flexible if the survey is to change.
我想到的另一件事是创建问题表和答案桌子.问题表将包含所有问题民意调查.答案表将包含调查中的个人答案，每一行都链接到一个问题.
The other thing I thought of was creating a Question table and Answer table. The question table would contain all the questions for the survey. The answer table would contain individual answers from the survey, each row linked to a question.
一个简单的例子:
tblSurvey:SurveyID
tblQuestion:QuestionID、SurveyID、QuestionType、问题
tblQuestion: QuestionID, SurveyID, QuestionType, Question
tblAnswer:AnswerID、UserID、QuestionID、Answer
tblAnswer: AnswerID, UserID, QuestionID, Answer
tblUser:用户 ID、用户名
我的问题是可能有很多答案使答案表非常大.我不确定那是不是很棒谈到性能.
My problem with this is that there could be tons of answers which would make the Answer table pretty huge. I'm not sure that's so great when it comes to performance.
如果您有任何想法和建议，我将不胜感激.
I'd appreciate any ideas and suggestions.
推荐答案
我认为你的模型 #2 很好，但是你可以看看更复杂的模型，它存储了问题和预制答案(提供的答案)并允许它们在不同的调查中重复使用.
- 一项调查可以有很多问题；一个问题可以在许多调查中(重新)使用.
- 可以为许多问题提供一个(预制)答案.一个问题可以有很多答案.一个问题在不同的调查中可以有不同的答案.可以针对不同调查中的不同问题提供答案.有一个默认的其他"答案，如果一个人选择其他，她的答案会被记录到 Answer.OtherText 中.
- 一个人可以参加多次调查，一个人只能回答一次调查中的特定问题.
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.
这篇关于调查的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
