时间绑定层次结构的最佳关系数据库表示 [英] Best Relational DataBase Representation Of Time Bound Hierarchies
问题描述
每个人的意见是什么是SQL中时间绑定层次结构的最佳表示形式？
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
第1天...
Business
|
|-Joe
| |-Happy
| |-Sneezy
| |-Doc(*)
|
|-Moe
|-Bashfull
|-Sleepy
第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.)
主要考虑事项：
- 更新层次结构
- 查看整个日期范围内的整个层级
- 报告层次结构中的整个子树
- 报告整个子树的日期范围
- 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...
- 每个团队或人维度表
- 其他事实表将使用这些ID（例如存储效果指标）
- 结构需要方便日期范围内的历史报告
- 使用ETL或触发器维护替代结构是一个选项
- 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.
推荐答案
这里有几个不同的相关性书籍 - 一组用于时间数据库，另一组用于RDBMS中的层次结构。
There are several different books of relevance here - one set is for 'temporal databases', and the other for 'hierarchical structures in RDBMS'.
- Snodgrass
- 日期，Darwen和Lorentzos在SQL中开发面向时间的应用程序 com / amzn / click / B001E4587Qrel =noreferrer>时态数据和关系模型
- Celko Joe Celko的SQL中的树和层次结构
- Snodgrass "Developing Time-Oriented Applications in SQL" (PDF available online at URL)
- Date, Darwen and Lorentzos "Temporal Data and the Relational Model"
- Celko "Joe Celko's Trees and Hierarchies in SQL for Smarties"
您的问题的棘手部分在我看来是：
The tricky parts of your question, it seems to me, are:
跨日期范围的整个子树
Viewing the whole hierarchy 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.
这篇关于时间绑定层次结构的最佳关系数据库表示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
