I have a question on the “Best Practice” for database structure in Bubble. I have some experience with using SQL databases.
Let me give you an example using the User table. Each user in the table will have a Primary Key (Unique ID in Bubble). One of the fields in the User table will be the “Reports To” field. This field would be a Foreign Key that references the Primary Key of the user’s manager. This is the way that I would do it using traditional SQL logic.
However, Bubble has a list of things that is a list of Primary Keys. My question is, should I have a list of Direct Reports on the Manager’s record instead of the manager’s record on the direct report?
Another scenario that is similar. I have 90 Marketing Lists for different marketing efforts and each list 100-200 prospect record. I will add the marketing list into it’s own table and then the prospects will be in another table. Should I have a field on the Marketing List table called “List of Prospects” that will contain the unique_id of each Prospect that is associated with that marketing effort or should I have on the Prospects Table a field called “Marketing List” that is the unique_id of the Marketing List that the record came from?
Bubbles db structure is a bit confusing to anyone with SQL experience, because like you mentioned, you don’t really see the key transactions.
My go to process is always sub links to master, especially if 1 master (manager in your case) can have dozens of of subs (reports). Much cleaner process (imo) to update a users “reports to” field when they get a new manager vs updating a list of reports for a manager.
So if I was building your app, I would do it as you have it set up, but reference the user not the unique id. Under the User table, have the “Reports To” field be a user type that links to the manager user.
For scenario #2, same as above, link to the prospects as a prospect field, not as a unique id. Just by looking at it, I would again build it so the prospects table has a marketing list field that identifies what marketing effort they are under. However, its also important to consider your unique situation; can a prospect be in multiple marketing lists? If the answer is no, then proceed with what I said above. If the answer is yes, then you might want to consider have the marketing list have a field for list of prospects.
@NigelG made a really good write up in the below forum that helps those with SQL background better understand bubbles db methodology. Deff a good read.
Thanks for the article. Since a thing can use a FK to relate it back to another thing, what is the point of the List field and when would I want to use it? It just seems duplicative.
I recomend approaching the bubble db by brain dumping the concept of FKs. In bubble, things relate to other things via things, not keys. Going back to your second example, here is where ‘list of’ comes into play:
You have a data type called “Marketing Lists” and a data type called “Prospects”. If you wanted the “Marketing Lists” to contain all of the prospects within those lists, you would have 90 entries (one for each marketing list) and you would have 1 field per entry that is “List of Prospects” with data type as Prospect, which would be a list of all prospects within the “Marketing List”. That is how bubble handles one-to-many relations.
Bubble gives you more options… you can point one way, point the other way, point both ways, or use a joining table. The “best” way comes down the nature and size of the data and how you plan to search/display it.
If you want to understand good frameworks for which ways to go, a couple of great resources are:
