Help on db-related search & performance considerations

Hi there,

I’m fairly new to Bubble that I discovered 2 months ago. I roamed this forum quite a bit for tips on DB best practises. I have this pet project to allow people to list their pre owned games for sale. I came up with the below but still have some outstanding questions.

A game belongs to one Category, has several Offers.
An Offer is linked to one Game, belongs to one user
A User has several Offers, several MeetupPoints, several Games (wishlist)

Here are the data types links I got:

  1. Game (finite amount: 1-2 thousands max)
  • List of Offers
  • Category
  • list of Users (wishlist)
  1. Category (finite amount: 15)
  • List of Games
  1. Offer (infinite amt theoretically)
  • Game
  • User
  1. MeetupPoints (max 3 per user)
  • User
  1. User
  • List of MeetupPoints
  • List of games (wishlist)
  • List of Offers

I am having a tough time writing the source field for one repeating group aiming to display offers based on game title (linked to searchbox input) and location inputs (one of User’s MeetupPoints’ being at less than X km distance from Y - X and Y being input forms).

  1. Because one User has several MeetupPoints, I can’t “Do a Search” where User’s MeetupPoints “is within”. I can only do “contains” and “is empty”. Any known workaround?

  2. Is it ideal to have MeetupPoints be linked to Offers indirectly through Users, or should I link MeetupPoints to Offers as well (I’ll need to keep MeetupPoints linked to Users for settings - also User’s MeetupPoints should = User’sOffers’MeetupPoints)

  3. From performance perspective, is it better to have the Source be a “do a search for” with several constraints, or better to do “do a search for” intersects another “do a search for” ? I’m familiar with the concept of removing largest blocks of invalids first (all the Offers that do have the name typed in the searchbox for instance), then moving on to finer criteria (among the previous results, those who are within same location range) but I’m not sure how Bubble works behind the scenes.

  4. I’m doing double entry for data integrity as you can see -just feels safer in case I need to go from one to another both ways. Apart from being cumbersome in the workflow, does this impact performance as well? I’m sure Bubble performance is affected when it needs to search within more entry, but could the fact that each entry has more fields affect performance too?

Thanks in advance for those who took the time to read me :slight_smile:

If you’re coming from a relational DB background, moving to this style of DB can be a bit challenging to designing a de-normalised database but once you get your head around it, it will fall into place fairly simply.

You need to make considerations about how you’re going to use “Do a search for” the least as it will use the most amount of resources of any relative query.

On the topic of the meet up points, if they’re geographical then you’ll need to look at installing the google map plugins so you can take advantage of their services. It will require getting a key, etc but the interwebs is full of information on how to do that.

Have you got an example of the data you’ll be entering? You might get more luck with help if you give some data for people to organise.

From a cursory glance at how you’ve described it though, your layout looks fine. But it’s difficult to say whether it’s the methodology or the schema which could cause performance issues.

Thanks for your reply Scott.

Noted on minimizing use of “do a search for”. What do you reckon are better alternatives?

I’ll have a look at the google map plugins, I thought my problem was more logical (managing to filter Offers for which at least one of their MeetupPoints are within range) but the plugin might give me a shortcut.

I thought the data inputs were self explanatory, apologies for that:

  • Users create their Offers (price) and link them to one of the Games listed (Games are created by admin). Users create their own MeetupPoints in their User profile, which propagate to all their Offers.

  • Users do a search for Offers for the game they want: search game title (among the Games list) and location (geographical place). The results are a RG that output Offers matching the game title and for which at least one of their MeetupPoints is within x distance from the input location.

It’s not about better, it’s about what’s needed at the time to get the best performance out of the task. So the alternatives could be different depending on the scenario.

If you manage the data like you’ve got listed, you shouldn’t need to use “do a search” for most of it because you’ve already got the data associated with a game, user or offer already available and you can use a filter on that list to get the result(s) you want.

1 Like

That’s very good yet simple advice: I hadn’t realised myself my structure allowed me to do without “do a search” completely.

I have a weird "Game’s Offers’ (Meetup_points:filtered)’ Offers when I want to display Offers according to name and Location within x distance, but with double entry integrity, that works like a charm.

Appreciate you took the time to help out !

No problem, glad you’re on your way :slight_smile:

I look forward to seeing what you’ve got in the future.