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
Business | |-Joe | |-Happy | |-Sneezy | |-Doc(*) | |-Moe |-Bashfull |-Sleepy
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 :)
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'.
- Snodgrass "Developing Time-Oriented Applications in SQL" (PDF available on the web 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 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