调查数据模型 - 如何避免 EAV 和过度非规范化？

Question

我的数据库技能充其量只是平庸，我必须为调查数据设计一个数据模型。我对此进行了一些思考，现在我觉得我陷入了某种 EAV 模型以及涉及数百个表的设计，每个表都有数百个列（和数千条记录）。一定有更好的方法来做到这一点，我希望论坛上的智者能够帮助我。

我的问题是：我应该如何在 RDBMS 中对调查问题的答案进行建模？必须使用 SQL Server。因此，替代数据存储系统应排除在本次讨论之外。 （当然，有些应该并且将会被评估，但请不要在这里。）我不需要整个数据模型的解决方案，目前我只对答案部分感兴趣。

我已经搜索过各种论坛，但我找不到真正的解决方案。如果它已经在其他地方给出，请原谅并给我提供一个链接，以便我可以阅读。

关于我必须处理的数据的一些假设：

1. 每项调查由 1 到 n 份调查问卷组成
2. 每份调查问卷由 100-2,000 个问题组成（请忽略 2,000 个问题确实听起来需要回答很多...）
3. 问题可以有多种类型：多项选择、自由文本、数字（如年龄、收入、百分比...）
4. 每个调查涉及 10-200 个国家（这些不是受访者。受访者实际上是这些国家/地区的人民。）
5. 取决于类型每份调查问卷由每个国家 100-20,000 名受访者回答。
6. 一个国家可以调整调查问卷，即添加、删除或编辑问题。
7. 一个国家的数据收集在该国家的单独数据库中。从一开始就不可能进行在线集成。
8. 所有国家的数据必须稍后整合。这意味着，例如，如果一个国家删除了一个问题，则该数据必须以某种方式从他们发送的内容中导出，以便在所有国家/地区实现统一的设计，
9. 我将不得不编写集成和清理软件，该软件需要与每个国家的数据
   1. 最后，需要将数据导出为平面文件、每个国家一个矩形网格和调查问卷。

我已经和不同背景的人讨论过这个话题，但还没有得出一个好的解决方案。我主要得到两种意见。

1. 习惯于使用平面文件（电子表格样式）进行数据处理和分析的领域专家投票支持具有大量表和列的非规范化结构，如上所述（每个国家和调查问卷 1 个表）。这对我来说听起来很糟糕，因为我了解到要避免使用宽表，在使用表时确定表中实际包含哪些列会很烦人，数据库将变得混乱，有数百个表（或者我什至需要建立多个数据库，每个数据库都有相似但有点不同的设计）等。
2. 面向对象程序员投票支持强烈的“标准化”设计，这将有效地产生一个包含所有受访者对所有问题的所有答案的中心表。该表要么需要包含 sql_variant 类型的列，要么需要包含不同类型的多个答案列来存储不同类型的答案（多项选择、自由文本等）。前者本质上是 EAV 模型。我倾向于遵循 Joe Celko 的观点，他强烈反对使用它（他称之为 OTLT 或“一个真正的查找表”）。后者意味着每行将包含设计上不适用的类型的空单元格。

我能想到的另一种选择是为每种答案类型创建一个表，即一个用于多项选择问题，一个用于自由文本问题等。这不是那么通用，它会导致大量的联合连接，我我认为如果发明了新的答案类型，我将不得不添加一个表格。

很抱歉让您厌倦了这些文字，感谢您的输入！

干杯， 亚历

克斯 PS：我在这里问了同样的问题：http://www.eggheadcafe.com/community/aspnet/13/10242616/survey-data-model--how-to-avoid-eav-and-excessive-denormalization.aspx

Answer 1

嗯，imgur 已关闭，所以我稍后会发布图片。

我认为这在关系模型中是完全可行的。我构建了一个 CDM 来展示我将如何做到这一点。

出站

需要 4 个实体来定义一个国家/地区的调查。一些家长调查、国家和问题清单。您的问题具有内部关系，因此当一个国家/地区“编辑”某个问题时，您可以跟踪该国家/地区提出的问题及其来源。您需要的另一件事是一个可能的答案实体/表。每个问题可能有一个相关的可能答案列表（多项选择或范围等）。这 4 个应该完全定义“出站”方面。

入站

“入站”方只是 2 个新实体，即被投诉人和答案。受访者很简单，只是该人的人口统计数据（如果您认识他们），并且在这里您可以包括与回国的关系。每个受访者都在特定国家/地区回答了调查。 （如果此人旅行或具有双重国籍，则该人可能与受访者是 1:n）

答案是基本的；它要么是可能答案列表中列出的选项之一，要么是已提供的。不要全神贯注于答案可能是数字、日期等的事实。要么是 FK，要么是字符串。

报告

报告是所有这些内容的结合...您将选择一个国家/地区和一项调查，获取问题和答案列表。

答案复杂性

取决于您想要在哪里进行计算。如果您使用 Varchar2(4000) 列作为用户提供的答案，则可以向问题添加属性来描述答案的数据类型。问：年龄？ DT：（0 到 130）之间的整数。然后，您的集成层可以进行验证，而不是由数据库强制执行。或者您可以有 4 列，其中一列用于数字、日期、字符和 CLOB。您的集成层将决定要使用的列。当您报告这些答案时，您只需使用 Coalesce() 选择所有四列。

这是 EAV 吗，因为“Answer”的数据类型有点模糊

不，不是。

EAV 模型将实体分解为属性列表。
像这样：

Entity     Attribute     Value
  1          Fname         Stephanie
  1          Lname         Page
  1          Age           30

因为您看到调查模式的“答案”列同时包含单词和数字，就像此处的“值”列一样，您认为它定义了 EAV。事实并非如此。就像我向该模型添加了 3 个数据类型列一样，它不会从 EAV 中更改它。

我非常讨厌它

有人告诉我，我正在调整的查询必须“尽可能快”。好吧，给我 10 亿美元和 30 年。 “等等，十亿什么？” “尽可能多”、“尽可能快”都不是要求。您可以验证数据库中您想要的任何内容...构建 Before 触发器的负载，瞧！大量验证。

年龄列的数据类型是什么？或者生日栏？取决于您的数据源是什么。一些较旧的记录可能只有月份和年份，或者只有年份，或者“大约”或“大约”某年。您不能只有一个数字列并进行“尽可能多的验证”。 NUMBER(2) 可能比 NUMBER 更好。所以现在你将有 NUMBER(1)、NUMBER(2)、NUMBER... 来表示“尽可能多”。

我认为你被绊倒的地方

将其视为概念数据模型，而不是物理模型。在这些术语中，调查是一个实体。 问题是调查的一个实体还是只是一个属性。如果您构建了每个表，那么您清楚地表明问题只是调查的一个属性，并且垂直存储它们使其成为 EAV。这个模型表明 Question 实际上是另一个实体。问题之间存在某种关系，例如“一个国家[可以]编辑问题”。有最初的问题和编辑过的问题。每个问题都有一个可能答案的集合。最重要的是，它们都是问题。在 EAV 中，我调用 fname、lname、bdate、age、major、salary 等……所有这些都是非常不同的东西，只是属性。在这种情况下，我们不会将发起调查的机构名称、发布日期、到期日期等作为问题。

让我换一种说法。你是联邦快递。您想要存储某些事件的时间戳。每次包裹进入或离开设施或车辆时。在提货卡车上的时间、从卡车上下来并进入第一个设施的时间、离开该设施并登上飞机的时间等。您是否水平存储它们？如何提前知道跳数？如果您垂直存储它们，是否会自动使其成为 EAV？如果是这样为什么。

您是一家气象公司，从全国各地的气象站获取临时数据。假设传感器设计为在温度变化+/-整度时发送读数。如果您存储的sensor_ID|timestamp|temp是一个读取表，那么它是一个EAV吗？每个读数不是传感器的属性，它们本身就是属于集合/系列的实体。

答案的垂直存储与 EAV 的共同点之一是其难以执行分析查询。如果您想要列出所有对问题 5 和 10 回答为 TRUE 但对问题 6 和 11 回答为 FALSE 的人，那么垂直完成时会非常困难。也许这就是您将其视为 EAV 的原因。如果您想这样做，则需要不同的存储。问题和答案的关系存储并不是最好的报告数据库。让我们回到联邦快递的例子。当行是垂直时，进行“传输”时间报告并不简单。

Answer 2

这听起来像是您正在解决一个常见问题：如何使用锤子来拧紧螺丝。

您列出的两种选择都不好，每种选择都有不同的原因。但这是因为您试图将特定的数据模型填充到关系数据库系统中。一个好的方法是超越关系数据库，查看一些其他数据库/存储系统，尝试结合起来，找到最适合您的项目的方案。

---

我尝试过 EAV 模型并放弃了，因为它太复杂了，而且我不敢尝试使用关系数据库系统的多表模型。我发现的关系数据库最简单的解决方案是：将每个完整的响应存储为单个 CLOB，序列化为 JSON 或 YAML（或其他轻量级的内容），并存储在响应表中。

create table responses (
  id uuid primary key,
  questionnaire_id uuid references questionnaires.id,
  data text
)

Answer 3

如果我使用 SQL Server，Express 就可以了，那么我会这样做：

• 带有问题列表、标志的表
  对于类型（位），如果需要标志
  （位），正确答案（如果存在），
  等等
• 包含国家列表的表格
• 国家和地区的链接表格
  问题（某些国家可能无法得到某些问题的
• 答案表，其中包含以下列：
  问题和 xml
  可选问题栏
  包括那些添加的内容。

如果您不熟悉分解 XML，则对所有可选问题使用稀疏列。我不太记得表中稀疏列的数量限制，但我相信它超过 30,000。 SQL Server 在内部将稀疏列存储为 XML，并且当选择该列并且可以对其进行索引时将其粉碎。

下图显示了使用 SQL Server 创建的图表。 AL_A4 列将保存 QL_Id = 4 的答案，并且类型为稀疏。 QuestionList 表中的 QL_Id 未标记为必需，让您知道使 AnswerList 中的列稀疏。

由于国家/地区将添加问题，因此会创建 QuestionListCustom、QuestiontoCountryCustom 和 AnswerListCustom 表并添加自定义问题中的信息。

我确信还有其他方法来设计存储，这就是我交作业的方式，如果这不是作业那么你肯定为联合国工作。

Answer 4

您是否考虑过不重新发明轮子？已经构建了开源调查应用程序。即使它们不能满足您的需求，也可以下载一些并检查它们的数据模型。