代码之家 › 专栏 › 技术社区 › MatBailie

时间限制层次结构的最佳关系数据库表示

hierarchy reporting database sql

MatBailie · 技术社区 · 16 年前

在每个人看来,什么是SQL中时间限制层次结构的最佳表示形式?

我的意思是:
-在任何给定的日期,您都有一个正常的树层次结构
-此层次结构可以每天更改
-在任何给定日期,每个孩子仍然只有一个家长。

第1天…

Business
 |
 |-Joe
 |  |-Happy
 |  |-Sneezy
 |  |-Doc(*)
 |
 |-Moe
    |-Bashfull
    |-Sleepy

第2天…

Business
 |
 |-Joe
 |  |-Happy
 |  |-Sneezy
 |
 |-Moe
    |-Doc(*)
    |-Bashfull
    |-Sleepy

在任何时候,子级都可以第一次加入层次结构,或者完全离开层次结构。(例如,新员工和退休员工。)

主要考虑事项:

更新层次结构
在日期范围内查看整个层次结构
报告层次结构中的整个子树
在一个日期范围内报告整个子树

我知道我现在是怎么做的,但我对别人怎么做很感兴趣。

编辑

我天真地假设了一些考虑,所以会更加明确…

每个“团队”或“人员”在其他维度表中都有唯一的ID
其他事实数据表将使用这些ID(例如,存储性能指标)
该结构需要促进跨日期范围的历史报告
使用ETL或触发器维护替代结构是一种选择

通用性是最重要的(仅构成通用关系模式的一部分),它与驱动报告的易用性(任何日期范围内树的任何部分)以及可靠更新的能力相结合。

4 回复 | 直到 16 年前

Jonathan Leffler 16 年前

这里有几个不同的相关书籍-一套用于“时态数据库”,另一套用于“RDBMS中的层次结构”。

Snodgrass“ Developing Time-Oriented Applications in SQL “(PDF可在网址上在线获取)
日期,达文和洛伦佐斯” Temporal Data and the Relational Model “
Celko“ Joe Celko's Trees and Hierarchies in SQL for Smarties “

在我看来,你问题的棘手部分是:

在日期范围内查看整个层次结构

在一个日期范围内报告整个子树

其他项目,如果不是直接向前,那么可以使用书中概述的技术,并按照其他答案中建议的行进行管理。部分问题在于理解这两个要点的含义。在某种意义上,它们是“相同的”;“整个层次”只是“整个子树”的一个特例。但更深层的问题是,“你想如何展示——形象化、表现——层次结构随时间的变化?”您是想比较开始时间和结束时间的状态,还是想看看中间的变化?您希望如何表示层次结构中个人的移动?

问题比答案多-但我希望这些建议能有所帮助。

Chris Morley 16 年前

这里可以放几张平桌子。对于每一行,我们需要列ID、名称、parentID和inactiveddatetime(默认为空)。为属于Joe的旧文档设置日期时间,指示该记录不再有效,然后将其移到存档表(为了干净起见),然后为新文档创建新行(原始行的近副本),其中moe的id为parentid。这种方法的缺点是被移动的人必须获得一个新的ID,这可能不方便。

Jonathan Leffler 16 年前

我可以考虑一些合理的解决方案,这取决于您的数据是如何使用的以及它是如何变化的。

1)假设今天的等级制度是最重要的。我将在每个记录中存储今天的层次结构和常规的parentid列。对于以前版本的层次结构,我有一个历史表

ItemId, ParentId, ValidFromDate, ValidToDate

每当层次结构更改时,都会向历史记录表中添加新行。

2)如果任何/所有层次结构都同等重要,我将存储一个基线层次结构,然后实现一个层次结构事务表。

TransactionId, ItemId, Action (Move/Delete/Add), DateTime, OldParentId, NewParentId

Janco 16 年前

table item(id, ...)

table item_link(parent_item, child_item, from_date, until_date)

链接将在一段时间内存储树的表示形式。

这个结构表示一个网络而不是一个简单的层次结构,但它支持在层次结构中移动事物,但也支持在时间上进行回顾。在应用程序逻辑中需要检查的一些事情是,不允许Joe在同一时间的层次结构中的不同位置进行链接。

使用connect by previous子句(在Oracle中),报告相对容易。

如果要指定关系上的其他数据,则其他详细信息可以与项或甚至项链接相关。