Simple Database Structure

Database structure. I’m building something where the user will be able to create a list of projects, each project will have a number of Headers (many) with around 30 text fields and each Header will have many Observations of around 5 text fields. I have set the database up in a traditional way so the Projects table has a Header:Header data type and the Header table has an Observation:Observation data type.
The Projects are set up in a repeating group as they are created ,and once the Project is created the Header detail and the Observation detail per header can be added.
Users might want to search the Projects by name, alphabetically or via dates.
My question is what is the best way to set this up if there might end up being thousands of Projects per User?

Will users be able to search for projects created by others, or should they specifically be limited to only the projects they created with their user account?

Hi, thanks for getting back. Initially I thought the User would only be able to search for only their created Projects, but more research has shown that it is a good idea to set up an Organisation that the User is part of. In my use case the organisation would generally have a maximum of 5 users. So any user should be able to search for any project.
To clarify: there would normally be a range of between 10 and 50 Headers per project and Observations would be a maximum of 200 in extreme cases but normally around 20. I’m not sure how important these details are.

Something you’ll need to be aware of is Bubble isn’t nearly as efficient loading a long list of things on any one thing; if you’re only ever going to have 5 users or so on a single organization, you can have a field called User on the Organization table, that you add each user for that organization to. You DoASearchFor Organization’s User = Current User. If you’re dealing with a many to many approach where there may be any thousands of combos, hundreds or thousands of users on one organization or vice-versa, it may instead be better to create a new data type called something like OrganizationUser, with 2 fields, type User and type Organization. You add to this table whenever a user joins an Organization. and Organization anytime you need to pull up a user’s Organizations or an Organizations users, you can just reference this table in your searches.

And if you need to cull the amount of searching that is happening, you could set a privacy rule so each user can only see an organization their user is associated with.
This would mean when they run a DoASearchFor the database will already automatically ignore the thousands or millions of other unrelated records, saving you search time and WU usage.

I believe this is the most streamlined way to go but I certainly welcome feedback from other stronger Bubble users! I hope this helps, or at least points you in the right direction.