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!