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

What i'm saying with this is:
- On a date you've got a normal tree hierarchy
- This hierarchy can alter from day up to now
- Each child still has only one parent on a date

Day 1...

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

Day 2...

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

Anytime, a young child can join the hierarchy the very first time, or leave the hierarchy completely. (For instance, new employees, and upon the market employees.)

The primary factors:

  • Upgrading the hierarchy
  • Viewing the entire hierarchy across to start dating ? range
  • Confirming on whole sub-trees inside the hierarchy
  • Confirming on whole sub-trees across to start dating ? range

I understand how I actually do it at the moment, but am intrigued regarding how others may get it done :)

EDIT

I naively assumed a couple of factors so could be more explicit...

  • Each 'team' or 'person' may have a distinctive ID inside a dimension table elsewhere
  • Other fact tables uses individuals IDs (storing performance metrics, for instance)
  • The dwelling must facilitate historic confirming across date ranges
  • Utilization of ETL or triggers to keep alternative structures Is definitely an option

The generic character is most significant (developing only one a part of a normal relational mode), coupled with simplicity of use for driving report (for just about any area of the tree across any selection of dates) and a chance to be up-to-date dependably.

You will find a number of different books of relevance here Body set is perfect for 'temporal databases', and also the other for 'hierarchical structures in RDBMS'.

The tricky areas of your question, it appears in my experience, are:

  • Viewing the entire hierarchy across to start dating ? range

  • Confirming on whole sub-trees across to start dating ? range

Another products are, otherwise straight-forward, then workable while using techniques layed out within the books, and across the lines recommended in other solutions. Area of the issue is being aware of what individuals two summary sentences mean. In a single sense, they're 'the same' the 'whole hierarchy' is simply a special situation of 'whole sub-trees'. However the much deeper real question is 'how would you like to demonstrate - visualize, represent - the alterations within the hierarchy with time?' Are you currently trying to compare the states in the beginning and finish occasions, or are you currently trying to begin to see the intermediate changes too? How would you like to represent the moves of the individual inside a hierarchy?

More questions than solutions - however i hope the pointers are a handful of help.

A few flat tables can function here. For every row, we want posts ID, Title, ParentID, and InactivatedDatetime (which defaults to null). Set the datetime for that old Doc owned by Joe showing that that record is no more valid and move them back for an archive table (for hygiene), after which produce a new row (a near copy from the original row) for any new Doc with Moe's ID because the ParentID. The drawback with this particular approach would be that the person being moved must customize the ID, which might not be convenient.

I'm able to think about a few reasonable solutions, for the way your computer data has been used and just how it changes.

1) Presuming present day hierarchy is an essential. I'd store present day hierarchy having a conventional ParentId column in every record. For previous versions from the hierarchy I'd possess a history table of

ItemId, ParentId, ValidFromDate, ValidToDate

Whenever the hierarchy changes, you give a new row towards the history table.

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

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