I’m not very experienced with databases, but my understanding with a normal relational database is that it is highly desired to only have one relationship “path” to define a relationship between a foreign key and a primary key of another table. For example, say you had one table named “Country” and another table named “City.” Each country can have multiple cities, but each city can only have one country. In a SQL database I believe you would create a field in the Cities table which could be called “Country,” with a data type matching the primary key of the Country table. Then, anytime a city is added, it references the appropriate country. But, you would not have a field in the Countries table called Cities (list), because (a) some database purists would not want multiple values (cities) in one field, and (b) it would create a duplicate relationship that is prone to error/violation. If this method was implemented in a SQL database, for example, it could cause a lot of problems if the Country field in the Cities table does not match the Cities field in the Country table.
However, I understand that to facilitate the query structure of bubble, this is exactly what we are told to do: Add a “Country” field (type Country) to the “City” data type, and also add a “City” field (type List of Citys) to the “Country” data type. Then, each time a city is added, you do two things:
Add a new record to the City table.
Modify the appropriate Country record to add the City to the List of Citys field.
But what happens if only one step or the other is performed? It causes the contents in one relationship path to be different than the contents of the other relationship path. Is there any way that Bubble accounts or corrects for this? It seems like a big problem if the database has this vulnerability. Why is it done this way? If this is always the way it should be done in bubble, why not just make the user create the one relationship (similar to a normal relational database), and then do whatever behind the scenes coding that is required to allow the query in either direction–which would maintain the integrity of the database.
As a lifelong database guy, it took some getting used to the way bubble works. I especially don’t like the total absence of referential integrity. However, you can almost always find a workaround if you need to.
I’m not sure where you got the idea that both methods should be used. You have some compelling reasons why not in the post. You should pick one or the other.
My personal preference (and this might be because I can’t really wrap my head around why I would have lists of Things in a Thing) is to always use parent-child, one-to-many relationships. In your example, Country should be a parent to City. In the City Type, you should have a field of type Country and that’s it. If you need a list of Citys in a Country, do a search for Citys whose Country=“foo”. I find lists in a Thing add an unnecessary level of complexity.
There are a few really good deep-dives here on the forum - @NigelG has some great examples. Have a search for them.
Try to get rid of the big lumps first. So in this case you want to filter out the 9,900 orders that are not for this customer as early and quickly as possible.
You can do that by having a list of orders on the customer. Then the database knows exactly what 100 things to go and look at first to see if it was in January.
What you don’t want to do, is search each and every one of the 10,000 orders looking for January, and then take that and search through all the customers to see if this is for the customer you want.
List > Search Constraint > Filter > Advanced Filter
Try to get as much done towards the left of that as possible.
I have been reading a lot of @NigelG’s posts on this matter over the last few days, and the following thread is what got me pointed in that direction. In it, @NigelG states:
I usually frown at myself when I say this but … it all depends on what you are doing with your data access.
If you have, for example, a Conversation with multiple Messages…
Then you probably rarely wonder "Oh, here is a Message, I wonder what Conversation it is part of, as you will probably have that already.
So, in that case, you could dispense with the “parent” on the Message, and just have a list of Conversations. You really don’t want to search through every single message looking for the one for this Conversation and then find the most recent.
Conversely, if you had Employee which had a Manager you might not bother with the List on the Manager, and just have a parent. Particularly if you spend most of the time in Department or Team views. And also if this is actually the same “thing” so a User has a “is managed by” relationship with another User.
However, I do tend to make them bi-directional in a lot of cases if I am traversing the relationship both ways.
Yes, there are some risks that you get a mismatch. But that is really only if you are swapping parents. If you delete a “thing” it disappears from every list it is in.
Ah, but it isn’t multiple values really. It is more like a collection of pointers to objects (and an Object Orientated purist would look the database purist and wonder what all the fuss was about as they deal with bunches of objects all the time). Some “relational” databases also have this concept. And of course not all databases are relational.
I suspect that the answer to your questions is that maintaining Referential Integrity would be a substantial overhead, as it is not being done natively.
If you are worried about it, then work out the most frequent access or the largest nastiest access, and set it up for that.
I came across this conversation as I am addressing the same kind of challenge. Mine is a one-to-many relationship of Projects to Actions. I have a serious concern about maintaining integrity, especially because updates to Actions, including changing their Project, can occur in more than one place in the system (I just can’t bring myself to call it an “app”).
The approach I am working on to make this happen is to create a reusable element for all the “Create new”, “Make changes to”, “Delete thing” and other workflows needed for the Action type. These workflows will include appropriate updates of the list of Actions in the associated Project things.
By using the reusable element and its custom workflows religiously, all database updates will be done consistently regardless of what page initiates them.
This is still a work in progress. Any thoughts or cautions you share will be appreciated. When I get this working, I’ll be happy to share more details if you ask.
On the other side of this, I’m unclear on how to make searches as efficient as possible. Put another way, I’m uncertain how to search Actions for all those belonging to one project. This is what I was looking for when I came across this conversation.
Here’s a simple example of searching for the maximum value of PosnInProject.
Since the Type of the search is Action, this looks like it will search all Actions looking for the Project. Of course, what I want is to simply grab the Project and riffle through its list of Actions to find the highest PosnInProject value. I just don’t see how to do that with the Search for dialog.
Feel free to direct me to another conversation. I imagine this has been answered, but I didn’t find what I needed with the search phrase I used.
I don’t know if you have your data type fields set up this way, but the other way to do it without having to search through everything is to have a field in “Project” of type “Action,” which is a list of things. Then you can find “Search for Projects:first item’s Action’s” which would give you a list of all actions belonging to that project and then do whatever sorting or filtering you need to do. This way, insead of searching through all Actions looking for the ones with the correct project, you can search through the list of projects, which should be a much shorter list. I think that is kind of the point of what is discussed in the earlier posts.
