Hi everyone, forgive the rookie question but I’m still getting my head around database structure and good practice for the linkages between tables / data types.
For context: I am building career pages for a fairly large database of Employers (11,000). Each ‘Employer’ has various data types hanging off it
Eg
A list of Perks / Benefits that organisation offers it’s employers (Each Perk / Benefit is unique to that company so there could be a list of thousands)
A list of Jobs for that organisation (There are thousands of jobs)
A list of People who work there (There could be thousands of people)
A list of Images of their office (there are probably thousands of images)
A list of Industries that organisation belongs to (There are only 18 industries)
Each employer has a company profile page displaying all it’s relevant data.
So I’ve set it up so that I have Types for Employer, Perks, Jobs, People, Images & Industries
My question is how to organise the database for best performance and overall good practice in Bubble?
I can’t figure out whether it’s best to either:
Hang everything off the ‘Employer’ Type. Have 5 fields each being a list of things (Perks, Jobs, People, Images & Industries) & NOT have each type (Perks, Jobs, People, Images & Industries) have a field which then relates back to the specific ‘Employer’.
Don’t hang anything off the ‘Employer’ Type but rather have an employer field on each Type (Perks, Jobs, People, Images & Industries) that links to the Employer Type.
Do both 1 & 2.
Do a bit of both depending on the length of each thing (e.g Industry will only be 18 items.
Just wanting to optimise for speed and but not over complicate.
Your thinking about segmenting out the different types of data (versus trying to cram it all into one table) is in the right direction. (For example, instead of having a bunch of separate fields on the organization table for hosting images, treating them as their own object in the database, which enables you to do “more” (ex. add a caption, add tags, etc).
Generally, I end up with Option #3 (the combo) Here are some of my considerations for lists versus searches in Bubble…
If the list of things is relatively short and static, then I’ll save it as a list on a parent thing. (Ex. parent organization’s image_records)
I try to build with privacy roles in mind (ie. currently Bubble has some rightful limitations around complex searches in privacy roles. So, if you’re looking to enforce something granular, it’s easier to work with lists).
How I want to navigate from a search standpoint versus having to filter data (ex. if I want to look at all companies in a specific industry), this is a bit better to save “Industry” as a field on the company versus company on the industry.
I’ll try to distinguish between if something is specific to that employer versus if it is universal (ex. if it is specific, then typically I’ll defer to lists, but if it’s more universal, I’ll incorporate it as a single field in a search).
If you find yourself having to do a lot of advanced filtering or lots of conditional data sources to get results, that tends to be a sign that the model is missing some links.
And thank @dan1 for taking the time to give your thoughts. I’m a newbie in this community and it’s generous people like you that make this community wonderful.
Are there any downsides of just always building in the two-way linkages (e.g. just doing option 3). Seems to give the most options but just worried it, in some way may slow things down!? Although i have no idea!
The downside is really in the complexity of ensuring the integrity of the relationships. So you remove one side from a list, you do the other as well.
It is not so hard once your app is up and running and fairly stable. But in the early days when you are changing things rapidly, you can make a mess. I know I do !
Agreed. What I’ve found very helpful to keep apps well organized is to sketch out my entire database before building anything in Bubble. (Whether developing in Bubble or another languages, I think this is a good thing to do).
Intentionally sketching things out forces you to proactively ask questions about your model versus adding things on the fly. Doing so gets me to about ~90% of the fields that I actually end up using in an MVP candidate with a (hopefully) much less wonky structure.
Agreed with @NigelG’s point on removing from both sides of a list. Gets easier with time!
