FORUM COMMUNITY IDEAS SHOWCASE LEARNING CENTER

One to Many Relationship, how does Bubble maintain database integrity/logic?


#1

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:

  1. Add a new record to the City table.
  2. 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.


#2

Hi squigley

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.

Happy Bubbling!
Kirk


#3

Hi there,

This is actually the reason why it is often better to have it as a list of things in a thing.

Dependant on the size of your list, the search will generally take more loading time than the list of Citys in your Country thing.

@NigelG wrote a good explanation on this:

Think of it as a sieve…

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.


#4

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:

Then he goes on to say:


#5

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.


#6

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.


#7

I appreciate all the good feedback. I’ll take this account as I move forward on my first big project.