How to I create an intersection entity in Bubble?

I have Posts. I have Users. Users can make Posts as Done. I’d like to record the Done Date.

How do I do that in Bubble?

In SQL, I’d have an intersection entity eg Dones that would have a primary key for Post and a primary key for User. Ie:

Dones

  • userId
  • postId
  • doneDate

You can have an intersection table (or “datatype” in Bubble lingo) and do that.

More generally, the difference I’ve found between Bubble and classic database structure is the existence of the “list” field. This means that with many-to-many relationships, you aren’t required to use an intersection table (presumably Bubble does it somewhere in the background). So if a Post had a list of Users (ie authors), you could just create a field right in the Post table and add all the Users, which connects with the User table. As you can imagine this brings up discussions on which way to connect things. Should a Post have a field listing all the Users? Or should a User have a field listing all the Posts the User has contributed to? Or both? Or neither, and have an intersecting table?

There are some great discussions in the forum on these topics in relation to speed and scalability.

2 Likes

Thanks. I see the list field as a foreign key. Is that right? It sounds like I can just create a datatype as follows? I’ll try it.

Dones

  • userId
    -postId
    -doneDate

What I’m calling a “list” field contains foreign keys. As you’ve probably seen, when you create a new field in Bubble, you get a bunch of options like text, yes/no, date, etc, but also it shows you all the other datatypes (ie tables) you’ve already created. So you can select one of those to link entries to an entry or entries in another datatype. That could be a single reference, or if you pick “This field is a list”, then multiple references. You don’t need to tell Bubble which field in the other datatype to use to create that link; it does it automatically (though you can define which field Bubble shows for that datatype, by picking the primary display field).

In your case, if a Post can only be marked complete once, I’d avoid the intersection table and just create a field within Post with the completion data and the User who completed. But if a Post can be completed by multiple users on multiple dates, then you may have to go with the intersection datatype. I think the intersection datatype’s fields would be:

  • Unique id (this is a field Bubble automatically creates and populates)
  • Created By (this is an automatic field that Bubble creates and populates with the logged in user who creates an entry)
  • Post (you need to create this field and select Post as its type)
  • Created Date (this is a field that Bubble creates and populates automatically)

Per above, if your user is logged in, then the only field you have to create in the intersection datatype is Post. The rest Bubble automatically creates.

One final thing – I’d recommend some reading and experimentation on Bubble search/display. Ideal data structure in Bubble can’t be decided in a vacuum – it depends somewhat on how you plan on searching and displaying the data. So build out your search/display functions and test it on some dummy data to ensure you’re happy. This post is required reading in my opinion because it explores a lot of the considerations:

1 Like

Thanks! Yes, a Post can be Done by one or more Users, and a User can Done one or more Posts. So a true many-to-many relationship that I would use an intersection entity for in SQL.

Appreciate the post link and will read!