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?