历史表格数据的数据库设计与查询

Question

我有一组HTML表，用于存储一段时间内的调查问题和回答。每个问题都有自己的HTML表，列是年份，行是回答，然后每个单元格都有该年的回答数，如下所示：

​

​

我反复研究如何规范化这些数据并将其存储在数据库中，但我不确定最好的方法是什么。我正在寻找一个好的数据库模式，可以随着时间的推移处理额外的问题，响应和年份。我也在寻找一个好的查询，可以输出一个HTML表，如下所示。我可以在PHP循环中很容易地做到这一点，但我担心这对性能不好。

现在，我有以下表格设计：

问题

id int(11) unsigned AI PK
name varchar(255) UNQ
number varchar(255) UNQ
text

年

id int(11) unsigned AI PK
question_id int(11) unsigned FK
name varchar(255) UNQ (question_id + name)

响应

id int(11) unsigned AI PK
question_id int(11) unsigned FK
name varchar(255) UNQ (question_id + name)

数据

id int(11) unsigned AI PK
question_id int(11) unsigned FK
year_id int(11) unsigned FK
response_id int(11) unsigned FK UNQ (year_id + response_id)
count int(11) unsigned NULL

任何帮助或改进都将非常感谢。

Answer 1

因为question-independently.是，所以您不需要表year

和alter table data

• year_id int(11) unsigned FK到year YEAR
• UNQ (year_id + response_id)到UNQ (year + response_id)

感谢你的Rick James

Answer 2

通常，如果您有一个UNQ密钥(这是‘UNIQUE’的意思吗？？)，请为PRIMARY KEY使用if。

“名字”通常不需要是VARCHAR(255)。选一件小一点的。

"numbers“通常不需要是VARCHAR(255)。选择更合适的数据类型。

用CREATE TABLE语法编写您的模式；我在分析您的运行描述时遇到了严重的问题。

"0.00“代表什么？它是从其他数据派生出来的吗？如果是这样，就不要存储它。

从提供的第二张图片中，我猜您有一个表：

CREATE TABLE foo (
    year YEAR NULL,
    gender ENUM('male', 'female') NOT NULL,
    val SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY(year, gender)
) ENGINE=InnoDB;

我不明白“1959-1974年”是什么意思，但它可能是

    cohort VARHAR(20) NOT NULL

并在第二个表中替换gender，否则该表看起来与上表类似。

但是..。在不了解如何处理数据的情况下，您无法真正设计模式。你有没有试探性的SELECTs？

Answer 3

目标数据模型的结构化版本已经有了很好的想法-如果您希望统计数据的结构更加灵活，但仍然能够随着时间的推移进行键控和分组，那么另一种选择可能是遵循bi/ dw模式对您的数据进行建模

下面是“逻辑的”，并将与每个kimball et. al.的事实表中的属性/维度相关，其中事实表的“粒度”是“src html file + table + row + cell +value(S)”，假设您的值随时间保持一致

单元格(我注意到在您的图像中，一个html文件有两个表，每个单元格中有几个值(指向源html文件//

• /
• 中的位置，如果需要执行自动检查，也可以存储源html )(指向规范化的队列，例如。“随着时间的推移，18-24岁的人”，或者“time')
• group_question上的男性(指向问题定义-这是time)
• question_id上的所有相同的问题(问题定义+问题year)
• question_year (这是问题是asked)
• cohort_start_year的年份))(这是被问到question)
• cohort_end_year的队列的开始年(这是被问到question)
• cohort_start_age的队列的结束年)(如果适用，将是规范化的'xxxx - yyyy'，例如：'18')
• cohort_end_age (这是由'xxxx -
• ‘指定的或由’xxxx- present‘推断的，其中'present’是报表html文件的年份)
• values 1 .N必须计算相同的内容，否则您也需要将它们拆分出来，

要生成像样的输出，您需要最终确定数据表上的问题，但是无论您做什么，使用php导出html都是相对简单的。

我想过将数据加载到mysql中的方法，但如果没有作为数据源的html文件的可靠样本，就很难编写特定的代码(即。在浏览器中打开并“查看源代码”，或与之类似)

作为一般方法，我将使用php和DOMDocument解析html中的每个事实(表格单元格td)，然后以非规范化的形式发出一行，以便随后加载到临时表中，并最终加载到事实表中。

在这个上下文中，'emit‘是最终成为事实表中单个行的源，但是您还不能加载它，因为您不知道您的维键将是什么，除非您在解析html时定义它们

这实际上是不可能做到的:相反，加载到一个定义松散的表中(没有任何引用。完整性)，并在解析完所有文件后，编写etl或查询来生成维度表，然后再完成事实

(我可能会使用pentaho data integration来处理第二个阶段-它的流式xml解析器无法处理第一个阶段:太严格)

我发现this test html file已经足够陈旧了，以至于我一想到要无休止地重写抓取代码来解释“dreamtheaver”带来的永无止境的布局变化，就想吐出最后一杯咖啡……

一旦我的手足够稳定，血液流动恢复正常，我引导机器精神并产生以下php -值得注意的是，没有对源表进行任何形式的重组/去规范化：

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$dom = new DOMDocument();

$srcfile = 'testxmlparser.html';
$dom->loadHTMLFile( $srcfile, NULL );

echo 'odom is: ' . ($dom ? 'nice':'naughty') . PHP_EOL;

if( $dom ) {
  // get all the table rows in the document
  $tblrows = $dom->getElementsByTagName('tr');

  foreach( $tblrows as $trrow ) {
    $tblcells = $trrow->getElementsByTagName('td');
    $incr = 0;

    // buffer this table row's cell (td) data that we encounter, in case it is interesting...
    $srowbuf = '';
    foreach( $tblcells as $tdcell ) {
      $srowbuf = ($srowbuf . $tdcell->nodeValue);
      if( 1 <= $incr++ )
        $srowbuf = ($srowbuf . '+|');
    }
    // we know the table data we're interested in has 12 cells only
    if( 12 == $incr )
      echo $srowbuf . '+|' . $incr . '+|' . $srcfile . PHP_EOL;
  }
}

?>