I’ve run into an issue that is forcing me decide between correct database design vs. faster searches.
Good database design/normalization calls for eliminating redundancy. This is done by creating separate tables for each data type and establishing relationships between those tables.
For example, you have a table of Conferences. Instead of including the location of each Conference in that table, you create a Venue table that contains the location’s name, address, city and country. Then within the Conferences table, you have a field that links to a Venue. So far so good… each Conference is linked to a Venue. And when you display that Conference to the user, you can display the Venue’s fields as well.
The issue comes with searching. You’ll want your users to be able to search Conferences by city and country. However, those fields are contained within the Venues table, not the Conferences table. When selecting the search constraints for a RG of Conferences, Bubble doesn’t give “Venue’s City” or “Venue’s Country” as options. You just get “Venue”. So, you have to create a nested search (which looks like… “Venues = Do a search for…”)., where you create a search for “Venues” that match up with your city and country inputs.
Technically this works, but it’s very slow. I believe it’s because Bubble first searches for all the Venues that match the City and Country inputs, and then searches all the conferences that match the results of the Venues search. In addition to the search being too slow from a user perspective, I’m also concerned that it’s chewing through a lot of capacity.
The only solution I can think of would be to have “City” and “Country” fields in the Conferences table in addition to the Venues field. The would eliminate the need for a nested search, since they could be searched directly. This would speed things up, but it goes against everything that efficient database design calls for.
Am I missing any solution?