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.