StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are several different books of relevance here - one set is for 'temporal databases', and the other for 'hierarchical structures in RDBMS'.</p> <ul> <li>Snodgrass "<a href="http://www.cs.arizona.edu/~rts/publications.html" rel="noreferrer">Developing Time-Oriented Applications in SQL</a>" (PDF available online at URL)</li> <li>Date, Darwen and Lorentzos "<a href="http://rads.stackoverflow.com/amzn/click/B001E4587Q" rel="noreferrer">Temporal Data and the Relational Model</a>"</li> <li>Celko "<a href="http://rads.stackoverflow.com/amzn/click/1558609202" rel="noreferrer">Joe Celko's Trees and Hierarchies in SQL for Smarties</a>"</li> </ul> <p>The tricky parts of your question, it seems to me, are:</p> <blockquote> <ul> <li><p>Viewing the whole hierarchy across a date range</p></li> <li><p>Reporting on whole sub-trees across a date range</p></li> </ul> </blockquote> <p>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?</p> <p>More questions than answers - but I hope the pointers are some help.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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