时间界限层次的最佳关系型数据库表示法[英] Best Relational DataBase Representation Of Time Bound Hierarchies

本文是小编为大家收集整理的关于时间界限层次的最佳关系型数据库表示法的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

每个人都认为SQL中有时的层次结构的最佳代表?

我的意思是:
- 在任何给定的日期,您都有一个普通的树层次结构
- 该层次结构可以从一天到日期变化
- 每个孩子在任何给定日期仍然只有一个父母

第1天...

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

第2天...

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

任何时候,孩子可以首次加入层次结构,或者完全离开层次结构. (例如,新员工和退休员工.)

主要考虑因素:

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

我知道我目前是怎么做的,但是对其他人的做法很感兴趣:)

编辑

我天真地假设了一些考虑因素,所以会更明确...

  • 每个"团队"或"人"将在其他地方具有独特的ID
  • 其他事实表将使用这些ID(例如存储性能指标)
  • 结构需要促进跨日期范围的历史报告
  • 使用ETL或触发器维护替代结构是一种选择

通用性质是最重要的(仅形成通用关系模式的一部分),结合了易于驾驶报告的易用性(对于任何范围的日期,树的任何部分)和可靠更新的能力.

推荐答案

这里有几本不同的相关性书籍 - 一组用于"时间数据库",另一组用于" RDBMS中的层次结构".

  • snodgrass" 在SQL 中开发面向时间的应用程序在网上在线)
  • 日期,darwen和lorentzos"
  • celko" /a>"

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

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

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

其他项目是,即使不是直接的,也可以使用书中概述的技术以及其他答案中建议的线.问题的一部分是了解这两个子弹点的含义.从某种意义上说,它们是"相同的"; "整个层次结构"只是"整个子树"的特殊情况.但是,更深层次的问题是"您要如何展示 - 可视化,代表 - 随着时间的流逝,层次结构的变化?"您是否正在寻求在开始和结束时间进行比较,还是要看中间变化?您要如何表示层次结构中个人的举动?

更多的问题多于答案 - 但我希望这些指针有所帮助.

其他推荐答案

几个平桌子可以在这里工作.对于每一行,我们都需要列ID,名称,parentID和inactivativatedDateTime(默认为null).设置属于Joe的旧文档的日期时间,表明该记录不再有效,并将其移至存档表(用于清洁度),然后为新的DOC创建一个新行(原始行的近副本)以Moe的ID为parentid.这种方法的缺点是被移动的人必须获得新的ID,这可能不方便.

其他推荐答案

我可以想到一些合理的解决方案,具体取决于您的数据的使用方式以及如何更改.

1)假设今天的层次结构是最重要的.我将在每个记录中存储今天的层次结构.对于层次结构的先前版本,我有一个历史表

ItemId, ParentId, ValidFromDate, ValidToDate

任何时候层次结构更改,您都会在历史表中添加新行.

2)如果任何/所有层次结构都具有同等的重要性,我将存储一个基线层次结构,然后实现层次结构交易表.

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

本文地址:https://www.itbaoku.cn/post/597568.html

问题描述

What in everyone's opinion is the best representation for a time-bound hierarchy in SQL?

What I mean by this is:
- On any given date you have a normal tree hierarchy
- This hierarchy can change from day to date
- Each child still only has one parent on any given date

Day 1...

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

Day 2...

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

At any time, a child can join the hierarchy for the first time, or leave the hierarchy completely. (For example, new employees, and retired employees.)

The main considerations:

  • Updating the hierarchy
  • Viewing the whole hierarchy across a date range
  • Reporting on whole sub-trees within the hierarchy
  • Reporting on whole sub-trees across a date range

I know how I do it at present, but am intrigued as to how other people may do it :)

EDIT

I naively assumed a few considerations so will be more explicit...

  • Each 'team' or 'person' will have a unique ID in a dimension table elsewhere
  • Other fact tables will use those IDs (storing performance metrics, for example)
  • The structure needs to facilitate historical reporting across date ranges
  • Use of ETL or triggers to maintain alternative structures Is an option

The generic nature is most important (forming just one part of a generic relational mode), combined with ease of use for driving report (for any part of the tree across any range of dates) and the ability to be updated reliably.

推荐答案

There are several different books of relevance here - one set is for 'temporal databases', and the other for 'hierarchical structures in RDBMS'.

The tricky parts of your question, it seems to me, are:

  • Viewing the whole hierarchy across a date range

  • Reporting on whole sub-trees across a date range

The other items are, if not straight-forward, then manageable using the techniques outlined in the books, and along the lines suggested in other answers. Part of the problem is understanding what those two bullet points mean. In one sense, they are 'the same'; the 'whole hierarchy' is just a special case of 'whole sub-trees'. But the deeper question is 'how do you want to demonstrate - visualize, represent - the changes in the hierarchy over time?' Are you seeking to compare the states at the start and end times, or are you seeking to see the intermediate changes too? How do you want to represent the moves of an individual within a hierarchy?

More questions than answers - but I hope the pointers are some help.

其他推荐答案

A couple of flat tables can work here. For each row, we need columns ID, Name, ParentID, and InactivatedDatetime (which defaults to null). Set the datetime for the old Doc belonging to Joe indicating that that record is no longer valid and move it off to an archive table (for cleanliness), and then create a new row (a near copy of the original row) for a new Doc with Moe's ID as the ParentID. The drawback with this approach is that the person being moved must get a new ID, which may not be convenient.

其他推荐答案

I can think of a couple of reasonable solutions, depending on how your data is being used and how it changes.

1) Assuming today's hierarchy is the most important. I'd store today's hierarchy with a conventional ParentId column in each record. For previous versions of the hierarchy I'd have a history table of

ItemId, ParentId, ValidFromDate, ValidToDate

Any time the hierarchy changes, you add a new row to the history table.

2) If any/all of the hierarchies are of equal importance, I'd store a base line hierarchy and then implement a hierarchy transaction table.

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