❔ Hierarchy database design. Parent->Child->Grandchild->.... vs Material Path
My understanding of the differences between these two hierarchical patterns is that the former would be better when relations don't get too deep and you're mostly changing the relationships, whereas Material Path would be very fast for Reads and slower for Writes(changing relations).
To me, Material Path seems harder to implement but I believe fits my database model better. I found only one package for this (https://github.com/Meyhem/EFMaterializedPath) . It hasn't been updated for a bit.
Being new to the C# ecosystem, I'm just curious if anybody has implemented something like this and what their choice would be here.
I've implemented the former, and I'm seeing database queries of ~50ms at a root entity and its only going 3 levels deep. I imagine levels of ~10 or so with many more children than I currently have. I've gotten this time down to half or lower by fetching all entities upfront, but it concerns me a bit as the app gets bigger.
Looking for any input at all as I'm very new to doing something like this. Thank you!
GitHub
GitHub - Meyhem/EFMaterializedPath: Materialized path tree entity f...
Materialized path tree entity for EntityFramework core - GitHub - Meyhem/EFMaterializedPath: Materialized path tree entity for EntityFramework core
4 Replies
If it helps, my app is essentially monitoring large Fiber Cable runs that split off into child runs a few times, and a few times at each child as well. Each run is going to have certain additional relationships that i'll need to load.
a requirement is, for example, if I'm looking at a root run named "A" I want to show all data for not only A but for all its descendants as well. if i narrow down to one of A's children (e.g. "A1") , then i'd be showing data only pertaining to A1 and its descendants
moving an entity within the hierarchy should really only happen when a mistake is made when the user inputs data, so it shouldn't be often or its ok if it takes a bit of time.
Looks like nothing has happened here. I will mark this as stale and this post will be archived until there is new activity.
Still interested in some feedback if anyone has some. Thank you!
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.