Geek Answers Handbook

Better View of a Relational Timing Database

What is the best view in all opinions for a time-related hierarchy in SQL?

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

Day 1...

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

Day 2 ...

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

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

Key considerations:

  • Hierarchy update
  • View the entire hierarchy in a date range
  • Reporting for all subtrees within the hierarchy
  • Reporting for all sub-ribs in the date range

, , , :)

, ...

  • ""
  • (, )
  • ETL .

( ) ( ) .

+5
4

: " ", " ".

, , :

, , , , , . , . - " "; " " - " ". " - , - "? , ? ?

, , , .

+7

. , Name, ParentID InactivatedDatetime ( - null). , , , ( ), ( ) Moe ParentID. , , .

0

, , .

1) , . ParentId .

ItemId, ParentId, ValidFromDate, ValidToDate

.

2) -/ , .

TransactionId, ItemId, Action (Move/Delete/Add), DateTime, OldParentId, NewParentId
0
table item(id, ...)

table item_link(parent_item, child_item, from_date, until_date)

, , . - joe Sametime.

( oracle)

, .

0
source

More articles:


All Articles