BLUF: how can I recursively navigate relationships in a self-referencing table with an implied but not strictly enforced hierarchy?
I have three tables with relations to each other: Regions, Settlements, and Points of Interest (POIs). One of them follows the One Big Table schema. It lists all the “regions” in my world. There are many types of regions but they all have similar information about them I need to capture, so they’re in one table. But settlements have very different data from the various scopes of regions, so they get their own table. And Points of Interest (POIs) are different still, so they also get their own table.
Here’s what I mean by types of regions:
And here’s part of the Regions table:
You can see I have a self-referencing relationship in the table that says whether a row is contained in another row and vice versa. That creates an implied hierarchy but it’s not enforced strictly.
The problem happens when in Worldbuilding sometimes a POI is in a settlement (i.e. a forge, and sometimes it’s just along the road in a region, like a roadside inn:
In the example above, you can se that A Tavern is in the settlement Zarza which is in the Hexweave Valley. But Roadside Inn is not in a city. Its just on a road in the Hexweave Valley, so it has no relationship to a settlement. It’s related directly to an Area in the Regions table. And Loot Tavern is a tavern literally floating in the Astral Sea, so it isn’t in a settlement, an area, a region…none of that. It relates all the way to a Plane of Existence.
I am interested in being able to understand the full context of any POI (if it’s in a settlement, the settlement, if not, whatever region it’s in all the way up the implied hierarchy until it hits World) so that I can do things like provide a rich prompt to the AI asking it to help me flesh out the details of a POI. But the irregular nature of my jumps is stumping me.
I was able to use a formula to get to the region’s table regardless of if there was a hop into the settlements table first:
But there are so many possible combinations of jumps after that…I just don’t know what to do. What’s the best way to recursively query the “Contained in” relation until I’m not contained in anything?
Thanks,
Heidi
