In a nutshell, they suggest you start off with a one-way link by adding a field to the “subordinate” data type. But if the relationship will be used frequently AND the list will remain relatively small if implemented you can add another field on the “dominate” data type to create a two-way link.
I like this advice because I like simple rules of thumb.
But I’m wondering why they suggest starting with a one-way link on the “subordinate” data type, instead of starting with a one-way link on the “dominate” data type. What is the reason for this?
I find myself having to create a lot of “Do a search for” connections in the editor when the one-way link points from the “subordinate” to the “dominate” data type. Which if I understand correctly, will make the query slower.
I can reverse the direction from “dominate” to “subordinate”, but I’m early on in my database design and I want to make it as using best practice.
So what is the reason why they advise you point from the “subordinate” datatype to the “dominate” datatype?
When you say fetch do you mean, for example, “Current Page’s Tags”? And when you say search do you mean, for example "Do a search for” with condition “Tag contains Current Page’s Tag”?
Some Bubblers recommended using a separate detail Thing to reduce the data being fetched. For example, User and User-Details, or Post and Post-Details.
But I don’t understand what you said… which is why I was trying to clarify with examples…
Does the word fetch and search mean something different to my given examples?
How exactly does the idea that “if your Things have a lot of fields, search is cheap, fetch is expensive” effect the way I build my one-way links between my data types?
While the forum post you linked is very informative (I have read and bookmarked) I’m not sure how it applies to designing my database and the direction of one-way links.
Still trying to wrap my head around this all - so I appreciate your help
Sorry, I was getting ahead of myself. I made an assumption (which to be fair does apply to many Bubble apps) that you will eventually loading a Repeating Group (RG) with your Things.
Consider a RG loaded with Things with many one-way links vs an RG loaded with Things with few one-way links. Let’s say the search for both RG’s is the same: all Things created before a certain date. Now, even though the search is the same for both RGs (identical number of records matching the query), the first RG will take longer to load than the first RG --> because the first fetch (transfer of data from DB to RG) is heavier.
Thus, by using inverse linking, you can improve RG load performance, by making Things loaded in RGs leaner.
If a data type (Restaurant) has a related data type (Menu Items) and you try to load a RG with all Restaurants, you will be returned all the Menu Items as well, slowing down the load time of the RG.
Something you can do to keep the relation without causing the issue of slow loads is to set an identifying text field on the data type and when you want to relate one data type to another, you can set a text field on the other with the identifier of the first, so it is only a text field and not a data type.
Then when you want to find all the menu items of a restaurant you would do a search for menu items with constraint of restaurant identifier equals this restaurants identifier.
I am building a kind of a todo list app. Simplifying a little bit, here is the data structure : each user (data type 1) has a list of tasks (data type 2) and each task has a list of occurrences (data type 3) to appear in a calendar type interface.
I have read all the very useful information in this thread but I can’t decide on this question : should I use a two way link between the User (1) and the Tasks (2) data types ?
I understand the rule of thumb “Frequent requests + few items => two way link”.
In my case I do have frequent requests but I will have a lot of items (a user would typically create up to a few thousand tasks a year). So the rule of thumb would say one way link only, if I understand mainly for RG considerations.
But since there’s no reason the User type should be used in an RG, I tend to think that I should still use a two way link. Because it would allow to use requests such as “Current user’s Tasks” and “Current user’s tasks’ occurrences”, instead of doing searches in the whole database for a single user’s tasks or task occurrences.
Is this latter option a more efficient way of building my database and my requests ?
In other words, which way is better :
Directly look for 10k items among 1-10 million items in the database ?
Store the 10k items’ references in a user field and fetch them this way ?
@b.ramzi – I personally would avoid the long lists. A few things to think about, based on my understand of how Bubble works…
Bubble server-side searches which are straightforward are very fast and scalable. Also, Bubble will index data so that it can find the relevant data faster.
If you have a list of 10,000 items attached to the user, then the uniqueid’s for all 10,000 records will be downloaded to a page whenever you access information from that user record. That’s a lot of downloading taking place and on-page memory being used.
In addition (I’m pretty sure I read this)… if you have to do any sort of filtering or sorting of those 10,000 records that you are working with via the user list field, it happens client-side (on the browser), meaning that Bubble will have to download ALL of the data associated with each record until it finds enough results to fill whatever RG container you have. That can lead to some serious, app-killing slowdowns.
This is all on top of problems that massive list fields like this would cause if you ever needed to search and display user records.
A couple of resources that go into a lot more detail…
For situations where data type A has connections to multiple Bs (e.g. posts having categories but only one category per post; A = category, B = post), having a field on B that references the A it belongs to is generally better. Having a field on A that lists out all the Bs that belong to it is not going to work as well when that list can get very long
Thanks a lot for your insights !
I understand that searching is very cheap and thus looking for such optimizations (recording long lists of things) can do more harm than good.
I am coming back to this forum discussion pretty frequently. Thanks to everyone involved.
I know it’s been a while but i have a question that has been bothering me for a while now:
I have 4 linked (in a chain) databases, which are link together like so:
User → Company → Delivery → Deliverables
From what i’ve read, this isn’t the most efficient way. The other problem i’ve had with this approach is with the privacy rules which apparently don’t allow for such a long link (so “Current Users Companys each items Deliverys each items Deliverables” isn’t possible, but only “Current Users Companys Delivery” and now i can only select “contain” or “does not contain” as next operator)
I understood that it makes most sense to Link the sub-data to their “parents” like this: Deliverables —linked to—> Delivery —linked to—> Company —linked to—> User
The problem i have now is that i have to do several nested searches to find out things like which User should have access to which deliverables. That now looks like this: Example · CleanShot Cloud
What is the fastest and most efficient Database Linking structure i could follow in this case?
See this:
I’ve edited the answer now.