Databases
StackOverflow2013
Postsdbo
POBest Relational DataBase Representation Of Time Bound Hierarchies
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBest Relational DataBase Representation Of Time Bound Hierarchies
    primarykey
    data
    text
    <p>What in everyone's opinion is the best representation for a time-bound hierarchy in SQL?</p> <p>What I mean by this is:<br> - On any given date you have a normal tree hierarchy<br> - This hierarchy can change from day to date<br> - Each child still only has one parent on any given date </p> <p>Day 1...</p> <pre><code>Business | |-Joe | |-Happy | |-Sneezy | |-Doc(*) | |-Moe |-Bashfull |-Sleepy </code></pre> <p>Day 2...</p> <pre><code>Business | |-Joe | |-Happy | |-Sneezy | |-Moe |-Doc(*) |-Bashfull |-Sleepy </code></pre> <p>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.)</p> <p>The main considerations: </p> <ul> <li>Updating the hierarchy </li> <li>Viewing the whole hierarchy across a date range </li> <li>Reporting on whole sub-trees within the hierarchy </li> <li>Reporting on whole sub-trees across a date range </li> </ul> <p>I know how I do it at present, but am intrigued as to how other people may do it :)</p> <p><strong>EDIT</strong></p> <p>I naively assumed a few considerations so will be more explicit... </p> <ul> <li>Each 'team' or 'person' will have a unique ID in a dimension table elsewhere </li> <li>Other fact tables will use those IDs (storing performance metrics, for example) </li> <li>The structure needs to facilitate historical reporting across date ranges </li> <li>Use of ETL or triggers to maintain alternative structures Is an option </li> </ul> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJonathan Leffler
    1. USMatBailie
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBest Relational DataBase Representation Of Time Bound Hierarchies
      1. USVincent Buck
      1. VTFavorite
    2. VO
      singulars
      1. POBest Relational DataBase Representation Of Time Bound Hierarchies
      1. USDarrel Miller
      1. VTFavorite
    3. VO
      singulars
      1. POBest Relational DataBase Representation Of Time Bound Hierarchies
      1. USMrTelly
      1. VTFavorite
    1. CODo you care about time? Do you need to know what the tree looked like at a given time during the day, or does it move only day to day?
      singulars
      1. POBest Relational DataBase Representation Of Time Bound Hierarchies
      1. This table or related slice is empty.
    2. COAnd what does it mean to report "across a date range"? Say I ask for Mar 1 - Mar 31 and the hierarchy is different every day, then what would you show?
      singulars
      1. POBest Relational DataBase Representation Of Time Bound Hierarchies
      1. This table or related slice is empty.
    3. CO"What would you show?" That depends on the reporting need. It may be some aggregate value (If reporting on "Moe" for all of March then it could be the SUM() of a certain metric associated with his team members) It may be that the whole subtree is shown, showing the metrics for each node, in a way that represents the changes over time. That's up to the report. The important thing is that the model tracks the changes over time, in a way that can be relatively easily queried.
      singulars
      1. POBest Relational DataBase Representation Of Time Bound Hierarchies
      1. USMatBailie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload