I'm thinking these tables:

Set(

Id,

Name,

Dimension,

...)

Person (

Id,

Name,

...)

SetRelationships (

ParentSetId,

ChildSetId

)

PersonSets (

PersonId,

SetId

)

And then these tables give me a lot of my queries directly. The ones I want to denormalize would be

PersonContainedBy(

PersonId

SetId

)

SetContainedBy(

SetId,

AncestorId

)

Which would be a flattening of the hierarchy described in the tables.

I.E. if A contains B, and B contains C per the tables, Then C would have records for A and B in the ContainedBy view.

I guess my question is if I build a materialized (or indexed) view over a Recursive CTE, how performant will that be? If I change the underlying data, will it have to recalculate the whole view, or just the changed records?