What is the Best Practice for Data Relationships?

Need help Database
1

In a past life, I did a lot of work in SQL databases and now that I’m learning Bubble, I noticed when I create a table (or as Bubble strangely calls them “data types”). I have the option of creating a field that has a field type of a another table. So for example, let’s say I have a “Users” table and a “Groups” table and one of the fields in “Groups” is “Admin” which is a field with the Field Type set to “Users”.

On the “Groups” table, when I set the value of “Admin” to me@myself.com, Bubble is obviously not using an email address as the primary key for the table, but rather doing something behind the scenes to establish that relationship.

My question is: will performance be impacted/improved if instead of using Bubble’s relationship features, I use my own keys? So if I say every user has an ID which I calculate during signup, and then on the “Group” table, I set the Admin field = to the UserID, rather than the User field. That way they tables are connected on a unique ID that theoretically never changes. I know that if I set the field type to User and the User changes their email, Bubble will update the email address in the Group table as well, but I’m wondering what is better for performance - because if I use my own relationships, maybe Bubble will have fewer steps to go through in order to traverse the data model.

2

In short, an indirect light dB structure is the way to go. List fields should be minimized and used for relationships of 100 entries tops.

Here some interesting discussions:

Great video by @duke.severn showing pros/cons on different structures

Awesome case on the above initiated and substantiated by @lottemint.md

Best guide on building for performance (paid) by @petter

1 Like
3

Creating your own foreign keys isn’t a particularly good idea. It will make queries more complex than they need to be.

Limiting lists to “a couple of hundred” is a good idea.

So do the many to one…not the one to many as a list.

See my post on SQL Vs Bubble.

1 Like
4

Yes setting your own keys will give you a column that is not, or not as well indexed.

Set up a first data type (e.g. “Supplier”) then on your other tables, like “Product” you can now choose the data type of “Supplier” which means you’re using a better indexed column.

People berate bubble performance but we have over 120,000 rows in our dataset and do reasonably complex queries, we have stuck religiously to using these out of the box implied data types and never have any speed issues.

Richard

3 Likes
5

Agreed. Huge lists are tricky, but linking data types via field types works very well.

1 Like
6

Yeah, if bubble is as I believe it is, built on Postgres, then I think these implied data type columns are just indexed columns constrained to the key of the other table.

Put your linking data in a text or number fields and then search on that… you’re on for a full table scan if the data changes often.