Best-practice: Updating Data Real-Time vs Running Post-Process

[zachwhitt]

All- I’m working on an application that involves a few simple many-to-one databases. In this example, let’s say Locations has a one-to-many relationship with Projects where many projects will be completed at one location.

When a location is viewed, much of the information you need to know, quickly, is based on projects at that location. For instance, at the First Avenue location, the last project, Mercury was completed on 12/12/2018.

I’m struggling with how to do this and think there are three ways:

1. Join to the Projects database anytime I’m viewing a location and search for the correct record(s) or data to display. Very simple, but slow when viewing records with many on one page

2. Set an update flag on a location anytime any of that location’s child projects are updated. Then start a workflow to search all projects for that location and update the corresponding location data. Also simple. May have issues if multiple projects are updated at the same time?

3. Update the location record at the same time the project record is updated. I’m currently doing this, but quickly running into a consistency and upkeep nightmare as complexity is added

Any thoughts? How is the Bubble community does this? Keep in mind users will have the ability to edit information in the application (i.e. Mercury is marked Completed when it actually wasn’t, and the data needs to update)

I’m leaning towards option 2 above.

[zachwhitt]

Actually this forum post may answer my question:

Is there a way to have a database field calculate from other fields?

So I think what you are asking is … “Do I have to redo all those calculations across multiple tables each time I update something” ? The answer is probably “yes”. There is no way to have the database automatically update itself, or at least that I know of. What you can do is have a one custom workflow that rejigs everything. And you could run this “in the background” so to speak. So you wouldn’t need to do it on every page. You could have a single “function”. You could schedule this to run …

In my case, every workflow that updates the Projects table would set a flag on the Location record, which would then trigger a workflow to update that record.

[JohnMark]

@zachwhitt

May have issues if multiple projects are updated at the same time?

You really have to thing about it, as it can be a night… to debug when multiple users are on same projects or same locations. My take on take is to connect as much all databases together in form of list, so it faster to make any changes (speed is crucial).

@zachwhitt

Join to the Projects database anytime I’m viewing a location and search for the correct record(s) or data to display. Very simple, but slow when viewing records with many on one page

It will be faster if directly connected together (via _project_list or _location_list) so your point 1 is the best option (for me).

[zachwhitt]

Thanks. I do have the databases connected together I think how you’re suggesting. (Projects is a Lists field in Locations). My concern with #1 would be having to search for specific project data whenever a location is viewed.