title: RDMBS: Approach for efficiently accessing tree structure data held in a database
body: <p>Some details about the scenario:
   1. We have a MS SQL Server 2008 database which stores 'Resource' records in a table.
   2. Resources can have multiple child resources. 
   3. A child resource can belong to multiple parent resources. 
   4. Resources are linked to each other via an intermediary table, i.e. Resource -> Link -> Resource.
   5. There can be 'n' levels of parent\child relationships but realistically this is likely to be only 3 or 4 deep at most.
   6. Applications need to present a view of the data that shows the entire tree for a given parent and all its children where the children may be more the 1 descendant away from the parent, i.e. Resource (Parent) -> Link -> Resource -> Link -> Resource (Child)
   7. End users ideally need to see a real-time change in the tree based upon the actions they perform on resource relationships. By real-time I mean in the order of a few seconds as the change needs to reflected in the UI.</p>
  
  <p>I've done some exploratory work using a series of views but having to use outer joins means they views can not be indexed and therefore may not scale well.</p>
  
  <p>The next solution I'm looking at is creating one or more special tables that contain a flattened view of the resource trees. However this is unlikely to support the kind of real-time updates we require.</p>
  
  <p>Has anyone dealt with a similar scenario?</p>