Forum Academy Marketplace Showcase Pricing Features

Relational Database Setup

Hi All,

I have few questions on my database setup:

  1. In Figure 1, the primary “Recipe Listing” table is related to other tables (such as Recipe Steps). Is it correct to reference these other tables as “List of xxxx”, or should they be referenced as a single entry (xxxxx)?

  2. In Figure 2, the “Recipe Steps” table is shown. Each field contains a data array. Do I need to add a separate unique key field to link the two tables? Or, is it configured correctly using the Recipe Listing field?

  3. The data in the “Recipe Steps” table will only be uploaded via a CSV file import (i.e. there will be no “add data” option within the app). When importing the CSV file (Figure 3), if I choose the Type of Data field as “Recipe Steps”, I can validate and upload the data. If, however, I choose the Type of Data as “Recipe Listing” (as shown in Figure 3), I get an error (“The field Steps has been used twice. Please check your settings”). Is this error linked to an incorrect database configuration?

Thanks.

Figure 1

Figure 2

Figure 3

  1. Would seem fine, that looks like a good way to do it. You have also linked back from the listing step to the recipe (assumption is that a step is only relevant for a single recipe listing) which can be helpful. You might also want a list on the equipment and ingredient tables as well, as that can make life easier at times. But it does depend on what you are trying to do.

  2. I think this one will get complicated, I would suggest that having the multiple arrays isn’t what you want. You want a single “step” thing that has the step number and text rather than in two arrays. That way you can simply list out all recipe listing steps ordered by step number. You probably don’t want to have unique ids linking things, embeding (as you have done) is far far easier in almost all cases.

  3. You will need to have two different loads, one loads the recipe listing, the next loads the steps and links to the recipe. Not done one of these for a while, but you then might need to run a Bulk API to create the list of steps on the main Recipe Listing.

As I often say, rather than think about how to “store” the data, it is often helpful to think about how you want to access it. Then see if your structure supports that in a simple way. You spend far more time building queries for data than you do creating it.

3 Likes

@NigelG: Thank you for your feedback.

In the Recipe Listing data type, the “ID” field is the unique identifier. Should I include an equivalent “ID” field in the Recipe Steps data type to link the steps to the recipes? Else, how is the link achieved?

Yes, you will need the step to refer to the recipe I think, that would be useful anyway.

Hopefully that is the right way round, it is a while since I did a csv upload, but essentially you use a field to search on another thing to link it.

@NigelG: Thank you for your help.

@emmanuel / @NigelG :
Background

I created a sample app in https://bubble.io/page?type=page&name=recipe&id=forum_app&tab=tabs-1

I uploaded a sample TSV data file (primary delimiter = tab, secondary delimiter = | ) in three separate stages to the “Recipe Listings”, “Recipe Steps”, and “Recipe Equipment” data types. There’s a single entry in each of these data types.

Questions

  1. How do I display the Recipe Steps list in their repeating group?

  2. Do I need to constrain the search based on a key that links the “Recipe Listings” and “Recipe Steps” data types?

Thank you for your help.

When you uploaded the Steps, you needed to link the Step to the Listing.

Have a look at “Composite Field” here.

@NigelG: Thanks for your help. Unfortunately, I couldn’t get the repeating group to display the data (only the first item is displayed)

Here’s the link to the app: https://bubble.io/page?type=page&name=recipe&id=chefpert&tab=tabs-1

I experimented by uploading a CSV file (instead of the TSV).

I also reset the database structure to the original main data type (Recipe Listing) and defined multiple fields as “lists of texts” therein.

The “Recipe Listing” repeating group only displays the first item.

The “text” repeating group displays the entire list, but I would need to create multiple repeating groups to display the other fields.

Let me know if you can spot any glaring errors.

Thanks again.

That is going to be really awkward to do it the way you have done. i.e. display the 1st item from this list, and then the 1st item from this list. Then the 2nd item from this list and the 2nd from this list. etc

It will be far easier to have the “steps” as individual things within a list, and then just display that list in the repeating group.

1 Like