JOIN Tables Many-to-Many Relationship - Save in new Table - Detailed Example

Need help Database
1

Hi fellow bubblers!

I do need help with a database design question.

Short explanation: A User answers questions (Single and Multiple Choice) and gets a product recommended.

When the user answers a question, some rows in “user point allocation” and “user product point” shall be created, or if answers exist, updated (see tables below).
Thus, I want to JOIN “point allocation” and “User answer” using “question name” and “Answer name” as key, to fill in data in “user point allocation”.
Then, I want to JOIN “user point allocation” with “products” using “Apple” as key, to fill in data in “user product point”.

For “user point allocation”, I made a workflow where I deleted and then created the three entries separately (however, this is approach is not variable).
For “user product point”, I currently do not have a solution to ensure that all “Apple” are combined.

My questions:

  • How do I JOIN those tables properly? Maybe I think to SQLish - do I need another structure for bubble?
  • What columns are recommended to make separate things? perhaps question (name) or answer (name)?
  • Do you have other tips to make this workflow fast?

Thank you so much! I appreciate any helpful answer and feedback!

image
image1478×422 15.9 KB

&
image
image1114×228 7.1 KB

→
image
image1830×274 12.5 KB

&
image
image1102×320 11.8 KB

→
image
image1822×310 15.4 KB

2

Could it be that I need to create an object for every variable I want to join for this to work?

3

I think possibly you are thinking too SQLish for the Bubble Database lol. I think you need to have a table of questions with a field of question, answer, apple, points. Then on User you could have two Lists of Questions. One list labeled correct and the other incorrect. As the user answers the questions you can store them in the corresponding lists by the unique id based on whether they got them correct or not. Now you will not need to make a correct answered question and an incorrect answered question and you will be able to see which questions each user has answered as well as which ones were right and which were not. So now if you waned a total ‘score’ for a user it would simply be Current User’s List of Correct Questions: each item’s Points: sum.