Here’s an idea I’ve been reflecting on quite a bit. I hope it makes sense: Wouldn’t it be easier for building and scaling knowledge/data models if relations were many-to-many by default? I’d use one-to-one or one-to-many only when this restriction is indispensable for the model, but not by default.
I often get the impression that in most no-code apps with database features, there’s no feature parity between one-to-many and many-to-many relations in terms of views, grouping, filtering, lookups (or rollups), etc. For this reason, I often find myself unnaturally avoiding many-to-many relations, or modeling many-to-many relations by artificially reifying joined tables with one-to-many relations.
Fibery is the most many-to-many native among current no-code solutions that I’ve found, and I love how for example you can navigate hierarchical lists of many-to-many relations . However, there are still some limitations. For example, if the relations are between entities of the same database, hierarchical nesting in the list view is only possible for one-to-many relations. You can turn a one-to-many into a many-to-many but not the other way around. So it still feels like one-to-many is the default. It would be nice if you could start with many-to-many, and if you decide to turn it into a one-to-many when it’s already connected to multiple entities, a warning message could say “If you continue, 5 relations will be lost. Do you wish to procede?”
I’m not a DB expert, and I understand that one-to-many relations are computationally less intensive, but after decades of relational database optimizations I’d think that many-to-many relations could be the core from which other, more restricted relations derive. For most one-to-many relations I currently see in use, I see them as “This entity currently only has one related entity. In the future it may have more”. Even something that is taken as a given one-to-many, such as a date of birth. It could make sense further down the line to have two dates of birth for the same individual, the actual day she was born and her officially registered birthday. I could create another field or database for “Official date of birth” in addition to “Actual date of birth”, but this could add unwanted complexity.
Another example: Region → Countries → Customers. Most of the time this may be true. But occasionally you could have a customer that has transactions in two countries. And in some schemas, you could have countries like Mexico belonging to both North America and Latin America. Would it not make more sense when creating a relation to make it many-to-many by default, and then filter the relations that only have one associated entity? With hierarchical lists grouped by levels based on their many-to-many relations you can still have hierarchical views without losing the flexibility of many-to-many relations.
So, I’m curious, if many-to-many relations provide more flexibility to a knowledge model, why are one-to-many relations still so frequently used, and why haven’t no-code apps adopted a more many-to-many core from which other restrictions can be added further down the line?
I think the growing interest in graph databases comes from the flexibility they afford to knowledge models. At Neo4j they often say that with graphs, “the logical model is the conceptual model”.
“to make relational databases perform well enough for regular application needs, we have to abandon any vestiges of true domain affinity and accept that we have to change the user’s data model to suit the database engine, not the user. (…) The bottom-line problem with the denormalized relational model is its resistance to the rapid evolution that today’s business demands from applications. What we need is a model that is closely aligned with the domain, but that doesn’t sacrifice performance, and that supports evolution while maintaining the integrity of the data as it undergoes rapid change and growth. That model is the graph model.”
Graph schema does not modify the data model to fit a normalized table structure. The graph data model stays exactly as it was drawn on the whiteboard. (Neo4j - Put data modeling on the table).
There seem to be some implementation issues with graph databases. The fastest and most performant software I’ve used has relational database backends. For example, DevonThink and PanoramaX can reportedly handle millions of records without hiccups on a single laptop. More recent software based on graph databases like Roam Research aren’t nearly as performant. I saw that they prototyped Fibery on a graph database and it wasn’t performant enough. I don’t know why this is the case (at Neo4j they insist the graph model is very performant, though this may be more for traversals), I guess relational dbs have simply been much more optimized.
But with no-code tools, and in particular with Fibery, the end user that models the data doesn’t have to worry about the underlying structures. Fibery’s workspace map is unique in how it lets you precisely draw a data model on a whiteboard as you create your databases and fields. To the end user it doesn’t matter what the backend is like as long as it’s performant and it allows the flexibility to customize the app to their use case. Fibery stands out because it doesn’t have many of the limitations with many-to-many relations that Notion and others have. So, I wonder, what would it take for many-to-many relations became a kind of “central glue” from which all other relation types are further specified? Is there something that I’m missing, some reason why one-to-many relations are more prominent?
The ideal result would be that the user shouldn’t worry about finding a way to fit their model of many-to-many relations into one-to-many, just to not miss out on some of the features that one-to-many relations currently afford in existing no-code solutions. This would be complemented by the ability to add properties to relationships.
But I remember I had actually already done many-to-many lookups before and this was a main pull-factor for me.