Database functionality for static data

needy549 · 2024-09-09T00:32:45.340Z

Hello all!

I am working on a basic structure for a database and have run into a crucial issue that I am not sure how to solve in Bubble.

The app function: A tool that allows users to select certain sections of a codified book and add them to a project which then generates a report with only those items.

User flow: User creates new project >> User selects codes from static table >> User modifies / comments on / attests to the selected sections >> User clicks generate report which then allow for PDF print.

The current structure: Three tables: Users, Projects, Code-book.

image_2024-09-08_1927104701356×340 26.3 KB

The Problem: I need the Code-book table to stay the same and not change so users can all see the same text but they also need to select certain sections under their project and modify/attest/comment to their respective selections. I am not sure how to structure this or join tables in the most efficient way possible.

My current understanding of relationships: Currently, I believe that if I link the project and static tables together, (Project is parent and Code-book is child) then when a user makes a change to Code-book table it will show for ALL users?

What I think the solution is: I believe the solution is to create an additional table (call it a join) and name it Project-codes and push the selected texts from the Code-book table into it as selected so users can modify what they need without messing up the actual book for everyone else. This seems efficient and reduces the load tremendously but not sure how to push the data between tables or if it is even worth going down this road.

Possible Solution1474×381 120 KB

Any help much appreciated, this is possibly the biggest hurdle to overcome for this project.

codably · 2024-09-09T02:16:00.628Z

I think the structure you have suggested works.

The only thing I think is missing is to add references from the Selected_codes table to both the Project and the Code_book things so that you can query the Selected_codes by the associated project, and also you know which Code_book the Selected_code originates from.