I have a query about data structure. Imagine I have an app that allows a user to search for a hot drink based on a few criteria: a) drink type (coffee, tea, hot chocolate, other) b) cost c) distance from current location.
The search will return a list of drinks with name, coffee shop name, distance from location, cost, and the addresses will also be shown on a google map.
When you think about the data behind this, you can imagine a very large list of all drink items in all coffee shops, with one row per drinks item x coffee shop. However, as may of the coffee shops will be members of a chain of shops, it would be much better to have a database that has 2 tables:
a) a table showing a list of unique items per ‘coffee shop chain’ and
b) a table with a list of coffee shops which includes the field ‘coffee shop chain’
If a) had 5 items per chain and I have 10 chains each with 20 shops, then using one big table would need 5 x 10 x 20 = 1000 rows, while using the two tables gives table a) with 5 x 10 = 50 rows and table b) with 10 x 20 = 200 rows, so overall 250 rows. With real life data you can imagine the numbers would become very large for one big table.
Here is the question - how do I do the second method in Bubble? I can imagine how to do it in SQL with a query and where I have mapped the ‘coffee shop chain’ fields across the tables. But I do not see an option in the data tab where I can define such a link.