Many-to-Many Workarounds

[vitolytics]

Does anyone have a current best practice for many-to-many workarounds until the feature is done being developed?

[andrea-tr2]

Thinking of many-to-many as a "feature to be added" instead of as a fundamental aspect of the data structure already speaks volumes about this project.

It would be interesting to know the point of view of those who conceived it.

[FelixMalfait]

@andrea-tr2 that's not a very nice comment 😄

I've explained it before but we don't think many-to-many relationship belongs to the data layer at the same level as a traditional relationship. You don't have many-to-many relationships in Postgres or MySQL for example.
Say you're creating a field "previous employers" on "people", sounds like a many-to-many right? But then what if you want to log the job title for each previous position they held? Then you realize you need to access the underlying pivot table, that intermediate object cannot be hidden but should be exposed.

So building many-to-many cleanly means thinking about it at a layer of abstraction higher than the pure relation layer layer.
One of the key workstream today is to work on abstracting layouts (think field widgets etc). If we gain more maturity on this then we'll be able to build a better many-to-many relationship, as it would still remain two one-to-many at the data layer, but the user experience would shift. That's one of the reason we've been pushing back on this, we're not trying to get quick wins but to get to the right long-term choices :)

[vitolytics]

@andrea-tr2 that's not a very nice comment 😄

I've explained it before but we don't think many-to-many relationship belongs to the data layer at the same level as a traditional relationship. You don't have many-to-many relationships in Postgres or MySQL for example.

Say you're creating a field "previous employers" on "people", sounds like a many-to-many right? But then what if you want to log the job title for each previous position they held? Then you realize you need to access the underlying pivot table, that intermediate object cannot be hidden but should be exposed.

So building many-to-many cleanly means thinking about it at a layer of abstraction higher than the pure relation layer layer.

One of the key workstream today is to work on abstracting layouts (think field widgets etc). If we gain more maturity on this then we'll be able to build a better many-to-many relationship, as it would still remain two one-to-many at the data layer, but the user experience would shift. That's one of the reason we've been pushing back on this, we're not trying to get quick wins but to get to the right long-term choices :)

I appreciate what you are saying - but think doing this severely limits your user base. Take real estate, for example. What if you have many properties, and then many deal engagements. There needs to be the ability for the same property to be involved in multiple deal processes/engagements - but this is not possible under the current architecture.

[FelixMalfait]

It's possible with an intermediate object, but I agree the UX would be bad.
Note Pipedrive for example doesn't have many-to-many relationships and yet is used by 100k+ companies.

I also can't wait to have it! But we need to tackle things in the right order if we want to end up with a beautiful product

[andrea-tr2]

@andrea-tr2 that's not a very nice comment 😄

I've explained it before but we don't think many-to-many relationship belongs to the data layer at the same level as a traditional relationship. You don't have many-to-many relationships in Postgres or MySQL for example. Say you're creating a field "previous employers" on "people", sounds like a many-to-many right? But then what if you want to log the job title for each previous position they held? Then you realize you need to access the underlying pivot table, that intermediate object cannot be hidden but should be exposed.

So building many-to-many cleanly means thinking about it at a layer of abstraction higher than the pure relation layer layer. One of the key workstream today is to work on abstracting layouts (think field widgets etc). If we gain more maturity on this then we'll be able to build a better many-to-many relationship, as it would still remain two one-to-many at the data layer, but the user experience would shift. That's one of the reason we've been pushing back on this, we're not trying to get quick wins but to get to the right long-term choices :)

@FelixMalfait Fair point on tone, consider it a sign of passion 😄

OK, now let's get down to business: saying SQL doesn’t have many-to-many is like saying paper doesn’t have novels: technically true, but beside the point.

More formally: relationships in CRMs are conceptual before they are structural.
If we limit ourselves to what the data layer offers natively, we're just building tables, not modelling relationships.

In other words, deferring many-to-many to "future abstractions" is like shipping a calculator that promises multiplication, once the plus button is perfect. The abstraction layer shouldn't compensate for the data model... it should reveal it.

Hopefully not a not very nice comment 😄 All the best and thank you for Twenty.

[FelixMalfait]

Haha thanks for your reply. We'll get there!

[flomero]

maybe this but does not seam like a practical solution:
#12105 (comment)

[nghuyan304]

Many-to-Many requires a junction table (e.g., customer_tag(customer_id, tag_id)).
When users select via Multi-Select, instead of inserting/deleting directly in the junction table:
Temporarily store the selected IDs in a pending_relation_change table.
A background worker processes it → compares with existing relations → INSERT new ones, DELETE removed ones.

Fast UI response, no blocking.
Worker ensures consistency and avoids race conditions.

Change logs are kept (rollback/audit possible).

This approach is suitable for complex systems with heavy concurrent usage.

[FelixMalfait]

Internal issue: twentyhq/core-team-issues#188 (comment)