用于调查的SQL设计，具有不同数据类型的答案 [英] SQL design for survey with answers of different data types

问题描述

我正在进行在线调查.大多数问题的答案等级为1-5.如果我们需要向调查中添加一个问题，我将使用一个简单的Web表单，该表单会将INSERT插入相应的表格中，瞧！调查正在询问新问题-没有新代码或没有更改数据库结构.

I am working on an online survey. Most questions have a scale of 1-5 for an answer. If we need to add a question to the survey, I use a simple web form, which does an INSERT into the appropriate table, and voila! surveys are asking the new question -- no new code or change to the database structure.

我们被要求添加调查问题，这些问题可以包含不同数据类型的答案.规范要求调查是可配置的"，以便在将来的任何时候，当有人说:我们需要一项新的调查，询问{文本答案问题}，{1-5问题}，{真假问题} ，{带有日期作为答案的问题}"，我们可以在不更改数据库结构的情况下做到这一点.

We are being asked to add survey questions that can have answers of different data types. The spec is to have the survey 'configurable', so that at any point in the future, when someone says, "We need a new survey that asks {text answer question}, {1-5 question}, {true false question}, {question with a date as an answer}", we can do that without changing the database structure .

我正在尝试找到存储这些答案的最佳方法，但是我想出的每一种方法都显得有些拙劣.

I'm trying to think of the best way to store those answers, but every method I come up with seems somewhat hackish.

有些问题的答案可能是/否或是/否.有些问题的答案可能是整数(过去一个月中您使用过技术支持的次数是多少?")，另一种答案可能带有日期，字符串，具有单个值的多项选择，具有多个值的多项选择等.或者有时，特定的答案值可能会提示一个子问题(您对...感到失望的原因是什么?")

Some questions may have a yes/no or true/false answer, some may have an integer answer ( "How many times in the past month have you used tech support?"), another answer may have a date, a string, a multiple choice with a single value, a multiple choice with multiple values, etc. Or sometimes, a particular answer value may prompt a sub-question ( "What disappointed you about...?" )

简单的解决方案是将每个问题作为调查中的一列，将其答案作为调查中的一列，以及是否将其作为调查中的一列来询问.在我看来，这真是一团糟-那是一张大桌子；不是很关系".

The simple solution is to have each question as a column in the survey, its answer as a column in the survey, and whether to ask it as a column in the survey. This feels like a mess to me -- it's one big table; not very 'relational'.

头脑风暴，我能想到的最佳"方法是为每种答案类型使用不同的表，但是这容易受到数据完整性问题的影响.换句话说，我会

Brainstorming, the 'best' method I could come up with is to have a different table for each answer type, but that feels susceptible to data integrity issues. In other words, I would have

CREATE TABLE `Questions` (...);
CREATE TABLE `TrueFalseAnswers` (...);
ALTER TABLE `TrueFalseAnswers`
  ADD CONSTRAINT `TrueFalseAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE `TextAnswers` (...);
ALTER TABLE `TextAnswers`
  ADD CONSTRAINT `TextAnswers_ibfk_1` FOREIGN KEY (`question_id`)
REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

等

以上所述的一个问题是，我不能保证对于DDL中的任何问题，至少存在一个答案，只有一个答案.

One problem with the above is that I can't guarantee that at least one and only one answer exists for any question in the DDL alone.

另一种解决方案可能是在问题"表中为答案提供一个二进制或字符串列，并将所有答案编码为某种字符串或二进制格式，然后将其存储在答案列中.这样，每个问题至少给我一个答案，但是我却无法访问SQL查询中的聚合功能.这让我觉得这不是一个非常关连"的解决方案.

Another solution might be to have a binary or string column for the answer in the Questions table, and encode all answers into some string or binary format, and store them in the answer column. That gives me at least one and only one answer for every question, but then I lose access to aggregate features in SQL queries. This strikes me as not a very 'relational' solution.

因此，我发现上述想法存在问题.是否有解决此问题的最佳"方法?

So, I see problems with the ideas outlined above. Is there a 'best' way to solve this problem?

现在，我已经花了一些时间来表达问题和想法，看来我提出的主要问题是我想在不进行任何编码的情况下存储任意类型的数据... 这绝望了吗?

Now that I've taken the time to verbalize the problem and my thoughts, it seems that the broad problem that I'm posing is "I'd like to store data of an arbitrary type without doing any coding..." Is this hopeless?

我正在使用MySQL，所以我无法访问其他RDBMS可能提供的东西.

I am using MySQL so I don't have access to thing that other RDBMSes might.

推荐答案

使用一列指定答案的类型，但将答案存储为文本.您的应用程序或前端可以使用answer_type列确定向最终用户显示的内容(测试框，单选按钮，日期选择器)以及在将其发送回数据库之前如何对其进行验证.

Use a column that specifies the type of answer, but store the answer as text. Your application or front end can use the answer_type column to determine what to display to the end user (a test box, radio buttons, a date picker) and how to validate it before sending it back to the database.

这篇关于用于调查的SQL设计，具有不同数据类型的答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！