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.
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.
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.
Has anyone dealt with a similar scenario?