Question about Performance Differences

Is there any difference in performance between
(1) Doing a Search for all the related Things A and B in an intermediate table that relates them
(2) Accessing the List of Things B kept in Thing A?

Let me elaborate through an example:

Assume that there is a table of things called CLASS and another called STUDENT. Let’s assume a generic relationship between them:

  • CLASS has many STUDENTs
  • STUDENT attends many CLASSs

Assume also that I would like to navigate the relationship either way, i.e. from CLASS to STUDENTs and from STUDENT to CLASSs.

I could model this in in two ways in the database:
1) Use “List of” fields in each Type
In each type, create a field to contain a list of the other type (e.g. in CLASS, create a field called enrolled students and make it a list of STUDENTs and in STUDENT create a field called attended classes and make it a list of CLASSs

The big advantage I see here is the simplicity of the queries in the Editor because I can reference the list of STUDENTs enrolled in a CLASS without doing a search. Bubble may be doing that for me under the hood, but my work is simpler.

The big disadvantage is that when a new relationship is necessary, I have to update the list fields in both tables.

Finally, when a thing from one table is deleted, the list in the other is automatically updated for me because Bubble takes care of not leaving orphan records.

2) Use an intermediate table
Create an intermediate table called ENROLLMENT that contains two simple (i.e. not a list) attributes, one for each of CLASS and STUDENT.
Each Thing in this table represents one and only one instance of a relationship.

The big advantage is that changes in the relationships only have to be maintained in the ENROLLMENT table

The big disadvantage is that to create a list of related Things, now I need to Do a Search for. And that if I delete a Thing, I have to do the housekeeping of not leaving orphan records in ENROLLMENT.

Personal preferences aside, is there any material performance difference between the two methods?

Many thanks!


Performance for referencing the lists may be slightly quicker by using a List.
Obviously the workflow for adding and removing the items may be slightly Slower this way, because as you mentioned you are editing multiple things. Usually you are looking up and displaying the info more than you are editing, so the benefit is probably more seen by using Lists.

Personally I would use Lists, as long as a class never has more than say 1,000 records, and a Student never has more than 1,000 classes (long lists can slow things down) OR if you also want to store information on the Student to Class relationship, such as Grade, attendance, Date joined, etc.

Geoff | Top Shelf Templates
Check out a Best Selling Bubble Template
Check out one of The Most Used Bubble Template (FREE)

1 Like