调查数据库设计 [英] Database design for a survey

问题描述

我需要创建一个调查，答案存储在数据库中。我只是想知道在数据库中实现这一点的最佳方法是什么，特别是所需的表。调查包含不同类型的问题。例如：用于评论的文本字段，多项选择题和可能包含多个答案的问题（即检查所有适用的答案）。

我已经出现有两种可能的解决方案：

1. 创建一个巨大的表，其中包含
   每个调查的答案
   提交。每列将
   对应于
   调查的答案。即SurveyID，Answer1，
   答案2，答案3 $ /
   
   

   我不认为这是
   的最佳方法，因为有很多问题
   在本次调查中，如果调查发生变化，似乎不太可贵
   。




2. 另外我想到的是
   创建一个Question表和Answer
   表。问题表
   包含
   调查的所有问题。答案表将包含调查中的
   个人答案，
   每行与一个问题相关。

   
   
   

   一个简单的例子：

   
   
   

   tblSurvey ：SurveyID

   
   
   

   tblQuestion ：QuestionID， ，QuestionType，Question

   
   
   

   tblAnswer ：AnswerID， UserID ， QuestionID ，答案

   
   
   

   tblUser ：UserID，UserName

   
   
   

   我的问题是那个
   可能是吨的答案将
   使答案表很大。
   我不知道这是非常好的，当
   来表现。

我我非常感谢任何想法和建议。

解决方案

我认为你的模型＃2很好，但是你可以看一下更复杂的模型存储问题和预先准备的答案（提供答案），并允许他们在不同的调查中重新使用。

- 一个调查可以有很多问题;在许多调查中可以（重用）一个问题。

- 许多问题可以提供一个（预先制作的）答案。一个问题可以提供许多答案。一个问题可以在不同的调查中提供不同的答案。可以在不同的调查中提供不同问题的答案。有一个默认的其他答案，如果一个人选择其他人，她的回答记录在Answer.OtherText中。

- 一个人可以参与许多调查，一个人可以在调查中回答具体问题一次。

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:

1. 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.

2. 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.

   A simple example:

   tblSurvey: SurveyID

   tblQuestion: QuestionID, SurveyID, QuestionType, Question

   tblAnswer: AnswerID, UserID, QuestionID, Answer

   tblUser: UserID, UserName

   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.

解决方案

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屋！