Data types vs Option sets for improved perf and db mgmt

[rew.nudelman]

Hi there - I’m having some trouble understanding how to structure my option sets. I think I’d like to use option sets because I’ve read (bubble docs) that they could help with performance. I’ve achieved what I wanted to from a functional perspective using data types, but it would seem I would need to create 100’s of entries in order for each user to see every permutation of a WINE PAIRING. Here is a view of my current database: Winepairings | Bubble Editor

Is there a better way to structure my data (whether it’s with data types or option sets), so I can achieve the same end user result, but with better performance and db mgmt?

PS - I started to create some option sets, but I have no clue how to fetch, randomize, and constrain (ie - add conditions that exludes options/attributes?) the data from within the repeating group.

Thanks in advance!
-Andrew

[ed727]

Re: how to structure your database, think of it this way… if the wine pairings are subjective and don’t follow and hard and fast rules, then you’d need to hand create each pairing entry. However, if the pairings follow specific rules, such as if the meal is x and y but not z, then Wine A or Wine B, then you can put those attributes in the database and generate the pairings based on searches which look for certain combinations of things.

Re: option sets, there are some long posts on the forum which go into the ins and outs of them, but one top level takeaway is that in general option sets are best for smaller sets of data which are static and which don’t need to be behind a password.

On performance and data structuring, I highly recommend the below book. It’s essential reading on how Bubble operates, which then can inform your various database decisions.

The Ultimate Guide to Bubble Performance - the new edition is out (now 210 pages!)

[mockup-no-bg-no-shadow-websize] I’m very happy to say that after months of work, the new revised edition of The Ultimate Guide to Bubble Performance is out! The new version has more than 50 pages of new content, including a detailed guide on how to think about database structuring - one of the most requested guides from the first book. There’s also a full section on all the steps Bubble goes through while loading a page - @philledille you’re gonna like that one For those who ha…

[rew.nudelman]

Thank you so much @ed727 . I really appreciate the quick response. How do I setup the second point in bubble?
“…pairings follow specific rules, such as if the meal is x and y but not z, then Wine A or Wine B, then you can put those attributes in the database and generate the pairings based on searches which look for certain combinations of things”

[ed727]

It’s basically setting up the relational databases.

For example, let’s say you have the “Wine” database (or datatype, in bubble speak). You list the wines (Merlot, Cabernet, etc.).

Then have a “Food” database (cheddar cheese, red meat, salmon, halibut, etc.).

You now need to connect these databases. In the Food database you can have list field which references the Wine database, and in that field you can put all the Wines that match that food.

Or vice versa, in the Wine database you can have a Food field, and list all the Foods that match that wine.

(Or, you can do a 3rd database with entries that link each Food/Wine pairing, but we won’t go there right now; usually you don’t need to use it in Bubble).

With this simplistic example, searching is simple. Pull up a Food entry, and it will list the wines that go with it. Or if you’ve listed the Foods under the Wine database, do a search for Wine entries that contain that Food.

Now to get more complex, let’s say you want the person to pick a “Meal” of several items of Food (steak, sourdough bread, spinach, etc.). You can then search for Wines that match all or most of those food items.

To get even more complex, let’s say there are pairings to avoid (like never halibut and merlot). So in your database create a field that connects Wine to Food or vice versa, and that’s the “avoid” field (which exists in addition to the “match” field you’ve set up). So in your search, you’ll say find me all Foods that match this wine, but don’t match the avoid.

In terms of the “best” way to set it up, it depends on how large the database will get and what type of searching you want. That book I reference does a great job explaining all the different options.

Good luck!

[rew.nudelman]

This helps but I think we have another complexity we need to work through. Let’s say we have three consistent attributes on both wine and food. Attributes : body, flavor, color. Now what we want to do is say any wine that has a light body, never pair with a floral cheese. How would we setup the “avoids” on an attribute level?

[ed727]

Well, one way to do it… let’s sat you create a bunch of food attributes (which could be an option set) and then you connect the relevant attributes to each food item, and you also connect the relevant ones to each wine (desired food attributes, and/or undesired food attributes).

Then in your repeating group for wine, you can use an advanced filter search to find food items that line up with a wine because they share attributes, or alternately food items to be avoided because their attributes line up with the avoid field for a wine.

If you aren’t solid yet on repeating groups and search methods, it’s worth spending some time going through the tutorials and documentation to understand it.