Bubble lists versus traditional relational tables

Need help Database
1

Hi all. I’m a Bubble newbie, trying to get my head around how Bubble works versus my “old school” habits of database design and software development.

Specific question is around the List field type. If I had a common situation of users who belong to teams, and could belong to multiple teams, relationally I’d create a User table, a Team table, and a TeamMember table, the last of which would hold records that linked a User to a Team via those tables’ keys.

Bubble seems to offer Lists as an alternative mechanism to the above. Given that one would want UI capabilities to both edit a User to update their Team, and to edit a Team to update its Users, where should the List best live? On the User table as a list of Teams, or on the Team table as a List of Users? Does one option provide better ease of maintenance (of both tables)? Is there a rule of thumb about this?

Finally, I have a preference for logically deleting records, rather than physically deleting them. In my traditional database scenario above, I’d mark a TeamMember table record as inactive, rather than physically delete it, as a way to remove a User from a Team, but leave a data trail. Is this logical delete strategy something that can be done with Lists?

Hope that all makes sense. I’ve poked around videos and posts here, but can’t quite get my head around it, or see an obvious answer.

Thanks

TC

1 Like
2

My quick opinion on this is to stick with your connecting table method. You’re likely to want to keep properties that are unique to a particular relationship and that gets messy with lists.

You can traverse between the tables very easily as you’ll already have seen.

There is one gotcha which is that privacy rules on Bubble are not very kind to this type of setup: depending on your exact structure you will likely (with this setup) struggle to define rules due to limits on how many tables deep the privacy expression can be.

So in the end I end up also maintaining a simple access_permissions list on the user that keeps them in direct contact with the organisation table… purely for privacy rules.

The amount of redundancy that this creates is pretty minimal in the end.

All other functionality is provided by the main architecture above.

Just my thoughts.

2 Likes
3

Thanks Edward. That makes sense. And I like the defensive approach :smiley:. Maybe the privacy rules are rock solid, but I was worried about whether a bug or a misconfiguration with them could lead to a security or privacy breach.

1 Like