用于存储调查和答案的 SQL 结构 - 根据用户数据构建表和查询？
对于 SQL 来说，我完全是个新手。 我正在 ASP.NET 中创建一个网站来进行调查。 特权管理员用户将能够构建调查。 然后很多其他人会对调查做出回应。
一旦我有了新调查的一组问题，自动构建表和 INSERT 查询来存储答案的最佳方法是什么？
更新
感谢您的快速回复！ 我仍然有点不清楚在这种情况下如何最好地存储调查回复。 每个受访者都会得到桌子的一行吗？ 如果是这样，这些列是否是“针对第 1 列中的条目所确定的调查的问题 k 给出的答案”？
I'm a total newbie when it comes to SQL. I'm making a site in ASP.NET for doing surveys. A privileged admin user will be able to build a survey. Then lots of other people will respond to the survey.
Once I have the set of questions for a new survey, what is the best way to automatically construct a table and an INSERT query to store the responses?
UPDATE
Thanks for the quick responses! I'm still a little unclear on how survey responses would best be stored in this scenario. Would each respondent get a row of the table? And if so, would the columns be "answer given for question k of the survey identified by the entry in column 1"?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您不想为每个调查构建一个新表。
创建一个调查表（SurveyID、UserID、Name 等）
创建一个问题表（QuestionID、SurveyID、QuestionText、SortOrder 等）
（可选），创建一个选项表，假设您有多项选择类型问题（OptionID、QuestionID、OptionText 等） ）
然后，当某人创建调查时，您将其插入到调查表中，引用该人的 UserID 作为外键，然后获取新插入的 SurveyID，
然后对于他们添加的每个问题，将其插入到 Questions 表中，使用上面的 SurveyID 作为外键..等等。
编辑以回答您的编辑：
抱歉，我应该继续讨论存储答案。
您需要另一个名为 SurveyResponses 的表（ResponseID、名称等）
我将另一个表称为 ResponseAnswers(ResponseAnswerID, SurveyID, ResponseID, QuestionID, AnswerText/AnswerID) 其中 SurveyID 和 ResponseID 是各自表的外键，并且根据用户是否有多项选择或输入答案，您可以将其答案存储为文本（varchar）或作为选项表的另一个外键。
You dont want to build a new table for each survey.
Create a surveys table (SurveyID, UserID, Name, etc)
Create a Questions Table (QuestionID, SurveyID, QuestionText, SortOrder, etc)
optionally, Create an Options table, assuming you have multiple choice type questions (OptionID, QuestionID, OptionText, etc)
Then when someone creates a survey, you insert into the Surveys Table, referencing that person's UserID as the foriegn key, then get the newly inserted SurveyID,
Then for each question they add, insert it into the Questions table, using the above SurveyID as the foriegn key.. and so on.
EDIT to answer your edit:
Sorry I should have followed through to cover storing answers as well.
You would want another table called SurveyResponses (ResponseID, Name, etc)
And another table I'll call ResponseAnswers(ResponseAnswerID, SurveyID, ResponseID, QuestionID, AnswerText/AnswerID) Where SurveyID and ResponseID are foriegn keys to thier respective tables, and depending on wether users have multiple choice, or enter an answer, you would either store thier answer as text(varchar) or as another foriegn key to the options table.
您可能不想以编程方式生成架构。
相反，应该为调查、问题（属于调查）、响应集（每个用户一个）和问题响应（属于响应集）建立一个表格。
You probably don't want to be programatically generating the schema.
Instead, have a table for surveys, for questions (which belong to surveys), for response sets (one per user), and question responses (which belong to the response sets).
我会想到几个选项：
Survey表和Question表，其中每个Question都有一列DataType具有我们期望的响应数据类型的代码，以及可选的一些其他列/可用选项的相关表，该列指定该问题是多选/单选、强制/可选问题等。Answer表的 Answer 根据问题数据类型进行序列化/反序列化/索引/格式化等。I'd think of a couple of options:
Surveytable andQuestiontable, where eachQuestionhas a columnDataTypethat has a code of the response data-type we're expecting, and optionally some other columns / related table of available options, a column that specifies if this question is multi/single choice, mandatory/optional question etc. TheAnswertable's Answer is serialized/desieralized/indexed/formatted etc. according to the question data-type您不会自动构建该表。 该表将被预定义，答案将添加到其中。 我认为现在开始使用 ASP.NET 的最佳方法是深入研究 MVC 和 MVC。 Linq (http://asp.net/learn)
You wouldn't automatically construct the table. The table would be predefined, and the answers would be added to them. I think the best way to get started with ASP.NET these days is to dive into MVC & Linq (http://asp.net/learn)