What's the most efficient database architecture for Bubble child and grandchild tables?

Hi.

I’m re-evaluating my database structure and want to hear best practice for how folks have joined child and grandchild tables. One reason to get this right is that I’m calling grandchild tables from the API and the “Do search for” strings may be overly complex.

My architecture looks like the following:

Grandparent Tables

Parent Table

Which is the better way to structure the child table? Should the child table reference both fields from the grandparent table (left) or simply reference the parent table (right)?

More specifically:
Reference grandparent table:

OR
Reference parent table:

This may have implications for how I set up the API “do search for”, so I’d like to hear advice from the community.

What’s the best architecture that keeps the API calls simple?

1 Like

@emmanuel, could you provide some transparency into how bubble’s database works on the back-end so that we can design database schemas that work well for our bubble apps? Should we simply design our database as if it were to be build it in MySQL or Postgres, or are there other variables at play that might it impact bubble’s performance, scaleability, easy of use, etc.? …just looking for some best practices so that we can maker smarter decisions up front. Thanks!

You should really build it in a way that makes sense. We optimize structures behind the scene, but the whole point of Bubble is that you don’t need to worry about performance when designing your db.

4 Likes