I’m trying to figure out how to design my database so it’s fast at scale.
The app is like GoFundMe and a walk-a-thon. But instead of a single X-mile walk, it’s a bunch of workouts. The fundraiser succeeds when users hit their workout goal ("If I workout 100 times in 6 months, Animal Shelter receives $200 from me and $500 from 5 people who supported the fundraiser).
The most important data type is “Proof,” which is just a post showing that a user worked out with comments (like Instagram).
Other data types that use the Proofs:
Fundraiser: has a workout goal number and progress towards that goal (search for all Proofs for this Fundraiser)
Team: users create a team to reach a workout goal; same as a single fundraiser but more people.
Charity: the entity people are raising money and working out for (search for all Proofs towards the charity).
Company: like a Charity but is an organization consisting of individuals or teams that contributes to a Charity (search for all Proofs of the company).
The Proof is connected to basically every other data type, and I want to make sure I can display filtered Proofs quickly (e.g. only current user’s proofs, single fundraiser’s proofs, a charity’s proofs, etc).
I’m worried that searching for all Proofs and then filtering for the user, team, company, etc. will be too slow if there are millions of proofs (just 5,000 users hit 1,000,000 Proofs in about a year). A big Charity could have that many workouts alone pretty quickly. And a big Charity could have hundreds of Teams. Same with a Company.
If I understand Bubble’s doc on Creating a Data Structure, they would recommend creating a new data type like a joining table for Proofs and Charity, Proofs and Company, Charity and Teams, etc.
But I’m not sure if my understanding is correct or how to actually implement that.
I’d greatly appreciate anyone’s advice. I attached an image showing the data types.
