半结构化数据的数据库模型

Question

我正在寻找一个关于如何设置一个数据库来保存建模应用程序的数字数据的小建议。我的用户有一个电子表格，用于保存建模应用程序中使用的数据。数据的结构是这样的:每个选项卡是一个业务线，列是年份，行是元素。单元格是标准的十进制数、xx.xx等。

     2005 2006  2007  2008  2009  2010  2011 2012
data1   2.5   3.5  
data2    
data3   

第二个应用程序可以接收来自excel或odbc查询的ole链接的数据。我想把数据放到数据库(sql server或oracle)中，但我不确定如何构建表，平衡数据如何进入数据库，用户交互，然后将查询输出到第二个应用程序。业务线、元素和年份都不是固定的。

我知道它基本上是一个数据透视表，所以我看到的答案是一个包含{line，element，year，value}的表。考虑到与使用这种格式进出数据相关的问题，我会不会更好地使用一个包含{line，element，year1..yearx}的表，其中包含一些任意数量的列，以备将来使用？这不是实体属性值的典型情况，但有些类似。这些元素并不经常变化，但它们中有大量的300+。我可以将它们分组到单独的表中，并使用{line，year，element1..elementX}这样的结构，这可能是最简单的开发，但看起来并不“正确”。

输出查询通常保存在单个数据元素中，包含通过odbc传递给第二个应用程序的行、年份和值。

Answer 1

由于您计划使用关系数据库管理系统，因此我将把这些数据存储在一组规范化的表中。第一次尝试(这都是基于SQL Server 2005的，对不起，)：

CREATE TABLE MyData
 (
   LineOfBusiness  varchar(50)  not null
  ,Year            smallint     not null
  ,Element         varchar(50)  not null
  ,Value           float        not null
  ,constraint PK_MyData
    primary key (LineOfBusiness, Year, Element)
 )

在主键中有两个varchar(50)可能被认为是低效的，特别是当您最终拥有大量数据时。(a)我不会担心，直到您达到64k，但是(b)当您达到兆字节的数据时，再回去修改您的体系结构就太晚了--所以最好在第一次就把它做好。

将LineOfBusiness移到查找表中可能更有效：

CREATE TABLE LineOfBusiness
 (
   LineOfBusinessId  int          not null
    constraint PK_LineOfBusiness
     primary key
  ,Description       varchar(50)  not null
 )

如果“元素”可以在业务线之间重复，那么将其移动到查找表肯定会更有效率：

CREATE TABLE Element
 (
   ElementId    int          not null
    constraint PK_Element
     primary key
  ,Description  varchar(50)  not null
 )

年份是一个介于1900和2100之间的简单数值(如果不是，那么哈？)，所以没有必要对其进行规范化。年份的查找表是否有用取决于应用程序的要求。(也许在LineOfBusiness中使用FirstYear和LastYear列是有意义的？)

基于以上两个表，并在关系完整性中工作，您最终会得到

CREATE TABLE MyData
 (
   LineOfBusinessId  int       not null
    constraint FK_MyData__LineOfBusiness
      foreign key references LineOfBusiness (LineOfBusinessId)
  ,Year              smallint  not null
  ,ElementId         int       not null
    constraint FK_MyData__Element
      foreign key references Element (ElementId)
  ,Value             float     not null
  ,constraint PK_MyData
    primary key (LineOfBusinessId, Year, ElementId)
 )

这留下了许多关于如何加载数据和确保/保存有效性的问题，当然查询(可能还有枢轴查询)将需要编写，但如果您的初始存储设计不充分，您可能会旋转轮子而一无所获。

Answer 2

这不是EAV，它是一个不同的反模式，我称之为元数据摩擦。也就是说，它们看起来友好和方便，但它们往往会失控地繁殖。

定义第二个表，其中年份为一列，数值数据值为另一列。

不要试图为同一结果行上给定行的所有元素值编写查询。相反，可以使用返回多行的查询，并编写一些应用程序代码来迭代这些行，以收集所需的所有值。