调查查询系统的数据库设计

Question

我正在研究一个所谓的行为风险因素监测系统(BRFSS)，这是一个处理每年收到的调查问卷的网络查询系统。

我费了好大劲才想出一个合适的数据库设计。问题是:每个调查问卷包含大约80个问题，包括人口统计信息，如年龄、教育程度等，以及调查问题，如吸烟、健康等。每年，一些问题会改变，一些问题不会改变。数据来源是一个包含80+列的Excel文件。系统必须支持如下查询：

SELECT [question var], [demo var], count(*) 
FROM survey 
WHERE age in (...) AND educ in (...) [etc] 
GROUP BY <question var>

数据是只读的，即。导入后切勿更改。因此，它不需要进行太多的标准化。直观地说，一个类似电子表格的表格可以很好地完成wrt工作。速度和空间。然而，这就成了一个问题，因为问题会发生变化，那么我们就不能保留这个表中的所有数据，这是由于跨年查询所必需的。

我尝试将回答规范化到三个表中: questions、responses和response_values，它们可以支持问题变体。但是响应表在一年中跨越了98*14268 = 1,398,264行！这真是太大了。查询太慢了！

我应该如何设计数据库？如有任何帮助，我们不胜感激！提前感谢！

ps。我正在使用Python+Django+Sqlite。

Answer 1

您有没有检查过DatabaseAnswers，看看是否有可以用作起点的模式？

Answer 2

听起来像是星型模式的案例。

您将拥有一个(巨大的)事实表，如下所示：

question_id、survey_id、age_group_id、health_classifier_id、is_smoking ...、answer_value

和非规范化维度表：

age_group: group_name，min_age，max_age，age_group_id

对于这样的系统，140万行听起来并不多。

一些数据库具有特殊的功能来支持对这种模式的查询：

在Oracle上，这些将是：

用于支持累加的所有dimensions

• bitmap索引用于过滤低基数属性的is_smoking

• bitmap

• 连接索引用于过滤连接表中的低基数属性，即从事实表中选择但在age_group_id min_age表中过滤以处理用于预计算聚合结果的大型age_group视图

对于这种类型的数据，也有专门的db系统，称为多维数据库。

检查数据库是否有类似的结构，或者考虑切换数据库引擎

Answer 3

您至少需要3个表：

1)包含每个问题文本的Questions，带有自动增量id键

例：(123，“你的头发是什么颜色的？”

2) Questionaires，它将Q#映射到问题上。

问题#3上的问题#10映射到问题#123。

3) Answers，它将每个受访者与他们的调查问卷和数据联系起来

鲍勃对问题单#3上的问题#10的回答是“棕色”。

您应该看到使用现有问题和添加新问题添加新的问题单是多么容易。是的，将会有很大的表，但一个好的数据库引擎应该能够轻松地处理一百万个条目。您可以使用分区来提高效率，例如按年分区。

我将把它作为如何将其转换为sql的练习。