Database Structure vs Performance

Hi all,
I am new to Bubble and would greatly appreciate any advice from experienced users. There are plenty of topics on the forum about database design, but I did not find a definitive answer on using bridge tables.

My database has three main entities: Students, Courses, and Lessons. The idea is that a Student can complete (finish) both Lessons and Courses. Lessons are assigned to Courses, but one Lesson can be present in many Courses.

Here are the two structures that I assume could work:

In Approach 1 the tables Student’s Course, Finished Lesson, and Lesson in Course bridge many to many connections and allow additional attributes. For example, I can record which Lesson a Student completed and when.

Approach 2 utilises Bubble’s lists without bridge tables. However, my concern is that the list items can’t have any attributes which means that in the example above I can’t record the date when a Lesson was finished.

Moreover, when rendering the list of Lessons in a Course I will not be able to save the Position of a Lesson in the list.

My concern is that Approach 1 would mean a lot of filtered searches of bridge tables that have the potential to become quite large which I assume will reflect on performance.

I hope that I am missing something and there is the way to have lists with attributes without compromising performance?

Thank you in advance!

1 Like

I always love database optimization topics. Let me get some popcorn :popcorn:.

There are some real geeks :nerd_face: out here. I’m not one of them. But I just had a good nap so let me blurt out my 2 cents:

Go with option A.

I had a project with option B and I ran into a bunch of limitations whenever I wanted to find something “the other way”.

For example, I had a building with a list of apartments. Fine if I started from the building. Whenever I started a search from the apartment side, it felt heavy to make the search criteria and to load the results.

Yes, I used the verb “to feel”. Not a scientific approach.

Also, option A is how @joe5 advised me to do it. Apparently lists are fine when they have a limited amount of items. After 100 (?), performance goes down.

There is a long thread by josh about this: Performance Q&A guide

2 Likes

Thank you, Rico! I have the same “feeling” and I see a lot of design limitations in Approach B, but I also ate my share of popcorn reading through discussions defending both approaches :slight_smile: