In an interesting twist, experienced database developers may be at a disadvantage relative to newbies. It’s hard to wipe your mind of the standard relational model. I suggest you write a primer for people like me: “How Bubble’s database relations work, compared to the relational model.”
That said, I have a specific question. It’s the classic many-to-many model. I have organizations, which each have a list of projects and a list of staff. Each project has staff members associated with it. So, Projects-to-Orgs is many-to-one. Staff-to-Orgs is many-to-one. Projects-to-staff is many-to-many. (Many staff per project; many projects per staff.)
I assume I create a data type for each of Organization, Staff and Project. Then I add a list-type field in Organization for Staff and another one for Projects. Then I create a list-type field in Staff for Projects, and a list-type field in Projects for Staff. In other words, I’m assuming I do NOT need to create a new data type that does nothing but link Organization, Staff Person and Project, as I would conventionally.
This would be a cardinal sin in the relational world. Will it work here, or is my model wrong?
Ideally, when I’m assigning Staff to a Project, I’d like to see a drop down list of only the Staff associated with the Organization that is the parent to the Project. On the Staff page, I’d like to see a sum of the dollar values of all the Projects he’s assigned to.
There are even more complexities, but I’d be thrilled just to know if this relation is supported, and if I’m going about it the right way, and if not, what my mistake is.
Finally, what is the syntax for traversing the relation?
I just discovered Bubble a couple of hours ago. Not sure if it’s worth doing the course, because I’ve been writing databases apps for about 30 years. I’m hoping Bubble is the sweet-spot platform I’ve been searching for.