Join table or list field for many to many relationship

Hi,
I’ve been working for a long time on SQL database and I know that if there is a many to many relationship, you’re supposed to create a join table.

With what I’ve seen on the forum, apparently, bubble’s databases don’t work like SQL.

I want to have a table “Category” and a table “Group”. A group can have multiple categories and vice versa.

In term of performance for Bubble, is it better to use a field list “categories” in the Group table , or create a join table ?

1 Like

In bubble the term “data type” is referring to a table

The term “data field” is where you can define the data that it is associated with, most basically text or image. However, it is here you can make you relationship by using what is referred to as a foreign key by designating the data type as that of a data type in your database.

So I create a data type of hotels.

And a data type of amenities.

I want a many to many relationship between them so

In my hostels data type I create a data field I call “amenities_list” and designate it as a type of amenities, which is a reference to the data type I created called amenities.

In my amenities data type I put a data field of “hotels_list” which is designated as type hotel, which is a reference to the data type I created called hotels.

So to your question, there is no “join table” only a way to make a relation between data types and up to you to choose it as a list or a single item, essentially making many to many or one to one relationships based on your needs.

2 Likes

Bubble is fairly SQL like, it is the Relational aspect that is probably a little different.

http://forum.bubble.io/t/sql-joins-vs-bubble/25806

Instead of a Foreign Key pointing from child > parent you can either a) embed the parent in the child b) embed a list of children in the parent c) do both. You then just navigate the join without having to explicitly code it (like an SQL join).

To switch paradigms here … the “embedded list” in bubble is a bit more like a collection of objects (bubble calls them things).

However this list is neither sorted nor ordered (it is more like a set than a bag or collection).

So if you want to have a many-to-many that is more like a join table that has attributes (so you can sort by date added, or add in other attributes that describe the relationship) then that is the way to go. But you don’t have to.

@boston85719 Thank you guys for your responses.
I was worried about the performance, since it’s strongly not recommanded to create a list in SQL. But as I understand, there is no problem in that case since it’s pointing directly to the object itself.

So I can create two list fields, Thank you !

@NigelG Yeah, I’ve read many of your posts that helped me understand ! Thanks :slight_smile:

To add fuel to the fire, sometime you can’t create “two one-to-many lists” as in the case of adding a list of B objects to the A table and a list of A objects to the B table. Usually this occurs when you need to add attributes to the many-to-many relationship, such as when a group can have a primary category and groups can have expiration dates to which categories they may belong to, etc. In this case you are forced to use a bridge/join/xref table. Which is ideal anyway, if your entities could ever belong to more than 100+ of the other type (like if a group could be a part of more than 100 categories or vice versa).

I’;ve found Bubble handles this very well, you just have to change they way you think in terms of querying against it. In traditional SQL, you’d start with something like

Entity A join Bridge join Entity B
Where (predicates for A)
and (predicates for B)

In Bubble, you actually want to query against the bridge/join/xref table itself, where logically your query would look like

Do a search for XREF table
where entity A in (Do a search for A with predicates for A)
and entity B in (Do a search for B with predicates for B)
and (any predicates against the xref table’s attributes)

In most cases, the elements you’re providing queries for (such as repeating groups) will have the bridge table as the element type, so your usage for visual elements can look like

Data Source = Bridsge tables’ element A’s attribute

2 Likes

Re: So if you want to have a many-to-many that is more like a join table that has attributes (so you can sort by date added, or add in other attributes that describe the relationship) then that is the way to go.

Hello @NigelG ,
I am new to Bubble (not new to dev). I went the route of join table in Bubble, but I am not able to bring back the data through the join table to the GUI. Please help.

What I have is:
Projects table
Phases table
Activitys table, which contains the list of Phases
(this table is meant to house the possible list of phase/activity combinations for selection on any project)
Project Activitys, which contains the list of Projects and the list from Activity (which is Phase/Activity combo) and the start/end dates and status — join table

I now need to pull the list of project activities with their respective Phase and Activity titles (from the Activity table) for a specific Project ID which I have in the separate dropdown.

I am unable to achive this so far.

Can you help?