Many to Many relation

Hey,

We have a requirement to model a many to many relationship. How can this be achieved?

2 Likes

Hi! First-time user as of a few minutes ago, so not an expert... but it seems that, for now, many-to-many is not "out of the box", so you would need a standard relational cross-reference table.

Imagine you have Orders and Items. This is a many-to-many relationship (one order can have many items, one item can appear on many orders.

You would create an order table w/ order ID and your other order metadata, and an item table with item ID and item metadata.

Then, you'd create a third table, "orders_items", with two columns. One column is order ID with a picklist/rowlink to the order, and the other is a picklist/rowlink to the item ID.

This is the standard relational way to model this... but not sure if it "plays nicely" with the built-in app builder aspects of Honeycode yet.

Hi @IanR-ccbe and @Math-727d, welcome to the community and thank you for posting! @Math-727d is right, this is a good way of mapping many-to-many relationships. Another way we've seen customers do this is to include many columns in one table to represent relationship with another table. For example, if there was a Users table, and an Ice Cream Flavors table and if each user had no more than 3 favorite ice cream flavors, then it may be represented as three columns in the User table: Fav Ice Cream Flavor 1, Fav Ice Cream Flavor 2 and Fav Ice Cream Flavor 3, which are all picklists on the Ice Cream Flavor table.

Um, yeah - that's [a] way, but not an ideal way. At some level, it won't scale and by "level" I mean for most it will be a very few number of flavors. Is there no concept of a multi-select field in Honeycode?

Hi @Bill.French agree, it may not be ideal in all scenarios. There is no native "multi-select" concept in Honeycode yet. I'll be sure to pass on your suggestion to the product team so they're aware of this request. That said, it is possible to build something like the below:

Screen Shot 2020-06-25 at 11.33.42 AM

Using a table similar to what @Math-727d described above:

Picklist

1 Like

"Yet" being the key word there and I think this will emerge as a key requirement.

But this is a common challenge for near-codefree systems;

  1. Do you enforce a true relational model upon a user base who came to Honeycode to avoid such intricate nuances?
  2. Or, do you establish alternative ways to allow collections to be persisted as embedded objects inside parent records?

For many apps, the second approach seems like it has promise. Even Airtable has embraced the idea that (for example) transaction records could be managed as JSON data in a text field and they have added a JSON editor as well, the irony of which (in a code-free platform) does not escape me.

I believe there are opportunities in the code-free platform segment to rethink at least the one-to-many pattern and provide new ways to simplify relational behaviours.

3 Likes

4 posts were split to a new topic: Trying the route of using a joining table. But I can't get the syntax right