Do you connect data types to their grandparents with list fields?

[philnauta]

When you have 4 tables like this:
Parent
Child
Grandchild
Great Grandchild

Do you tend to put a List of Great Grandchildren on the Parent and Child? It makes the workflow take longer when creating the great grandchildren and it makes the database busier, but it makes searches and conditions that much simpler to create and understand.

Just curious how other people do it. Thanks!

[philnauta]

It seems that although it’s not considered best practice to duplicate data in a database, people are doing things like this because of Bubble’s speed issues:

Speed is an issue

@sergio.rufini. hi. We’ve found that nested “do a search for”s are particularly slow. While it’s typically frowned upon to duplicate fields in a database, we decided for performance reasons, we’d include the unique_id from the parent and grandparent tables in select progeny tables thereby reducing the extent of nested searches in our app. Pages that once took nearly 1min to load are now <3sec. Specifically, this is here’s what this looks from a database-perspective. [image] Say you want …

Speed is an issue

@sergio.rufini We follow something similar to above suggestion by @kramwe Instead of Grandparent_unique_id, we have ‘list of parents’ in the Grandparent object and ‘list of Childs’ in the Parent object. That helps us eliminate searches altogether in the pages.

I’m still not sure if the same logic would apply to my ‘list’ idea, but it seems to make sense. Please weigh in if I’m wrong, but for now, I think I’ll go for it. Cheers, Phil

[gaurav]

Not necessarily speed but also building cleaner, easy to understand workflows

[kramwe]

Since my initial post, we’ve more judiciously picked where we duplicate the FK fields in grandparent -> child tables. Basically, now there needs to be a good reason for us to do it. For example, we have a few tables that are 3 levels deep, and which render data to the User. For those, we’ve included the grandparent FK in the child tables, because we want the UI to be performant. Everywhere else, we take the performance hit.

[philnauta]

Interesting. Why are you willing to take the performance hit? Because duplicating fields has bigger downsides?

[kramwe]

Became too much to maintain and ensure that the FK and the FK$unique_id fields were always filled out every time a new record was created.