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!