Many-to-many relations as the pillar glue of LEGO-style no-code DBs

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.

1 Like

I find dealing with collections of entities is fundamentally different to dealing with one specific parent entity.

If I have a hierarchy of linked one-to-many relationships, there are things I simply cannot do if they were linked by many-to-many relationships.

For example, if I have a contact who has many orders each of which have many line items, writing a formula or creating a lookup on the line item database, I am able to access all of the parameters of the contact database, even though there is no direct relationship between the two. When going from a many-to-one relation, I can reach up through the hierarchy and access information easily and definitively, as the data is structured.

That would not be possible if the link was a many-to-many relationship.

Imagine if a single line item could belong to many different orders and each of those orders could belong to many different contacts.

It would be quite a messy situation indeed.

I find the difference in one-to-many and many-to-many relations enforces required structure in the data that keeps things in appropriate order.

I’d be interested to hear other’s thoughts.

3 Likes

Some basic operations that are simple with a -to-one relationship are awkward or impossible with a -to-many relationship - Like lookups.

With a to-one relationship, you can look up a specific field from the related entity. Like a simple join. And there are plenty of common data models that benefit from this simple ability.

2 Likes

Thanks a lot @webinit and @Matt_Blais
Those are very good examples. Lookups are the main reason I find myself artificially reshaping my data model to fit to-one relations, so that I still can do lookups (or rollups in Notion and Airtable).

I now more clearly see why lookups with many-to-many relations would be challenging. How would you keep that well defined path if you introduce multiple entities, right?

I definitely see the value of the kind of operations that -to-one relationship allow. I guess my question would be: wouldn’t it make sense to do some layers of abstraction under the hood, so that the end user could perform those operations with many-to-many relationships as well?

Take the example of products that belong to different combos each, and each of those combos were ordered by several contacts. So, a lemonade could be in both “combo A” and “combo B”. And “combo B” is then ordered by two different contacts. It would be interesting to have a look up field in products that showed all the costumers’ locations from where they’ve ordered combos that include product such as lemonade. But in order to do that, we have to take the extra step of figuring a data model that fits a -to-one relationship, as with line items and unique orders. I’m not against -to-one relations, I’d just like many-to-many relations to be able to have all the lookup and hierarchy options that to-one relations have.

I understand the added complexity and potential messiness from a programing perspective, but this complex messiness could be hidden away from the user of no-code or low-code solutions, emulating the elegance of graph models even if you still have relational databases with many join tables under the hood. Would that make sense?

Thanks for considering this rather bizarre line of thought!

1 Like

A little update: Fibery allows lookups between many-to-many relations. For a moment I thought it didn’t, but that’s because I hadn’t made a connection yet :grimacing: But I remember I had actually already done many-to-many lookups before and this was a main pull-factor for me.

So, I guess Fibery already has near feature parity between -to-one and many-to-many relations. Only many-to-many relations between entities of the same database can’t be viewed as a hierarchical list, unlike -to-one relations can.

Of course, if you use many-to-many lookups you’ll lose the specific path of each relation and you’ll only get an aggregate. If you need to trace that specific path, then you’ll have to use to-one relations, but that’s understandable. Maybe in the future some of the “abstractions under the hood” will allow you to trace the lookup paths in many-to-many relations too, with various -to-one joins under the hood.

So, I think Fibery is already quite close to that ideal of being closest to the user’s specific knowledge modeling :grinning:

3 Likes