Confused - How to create relationship between tables in database

I’m not understanding how to tell Bubble how the data in different tables is related.
I have a table for Products, a table for Variants and a table for Orders.

In SQL terms there is a one to many relationship between Products & Variants and a one to many between Variants & Orders.

I have set my primary relationships in Bubble like so:

This has allowed me to seemingly get the data I want to display in the front end:

But nothing gets shown from the Variants table, so I think I haven’t created the relationship properly. The frontend table displays info from the products DB table fine but not the associated data from the variants DB table or the orders DB table.

Any help appreciated.

Primary keys / foreign keys don’t exist in Bubble really. Well, behind the scenes they obviously do, but Bubble treats it differently (and I think they do it quite well).

You rarely need to use ‘ids’ in Bubble. The primary key of a Thing is it’s unique ID. But you don’t save that unique ID to a different thing to connect it, you just save the actual Thing. Yes, it sounds weird if you’re familiar with other DB structures.

Let’s suppose you have data as follows:

Product
Variant

On the Product data type, you’d have a field called Variant. The ‘type’ of this field should be Variant. So this essentially says, hey, this field contains a specific variant. On the Bubble side, that’s essentially storing the unique ID of the specific variant.

To reference that data, you’d just use Product’s Variant’s name/description/whatever field you want etc.

Also I’ll just throw in my preferred naming convention because bad naming can confuse people and I’ve seen a few examples these last few days…

  • Data types should be singular and capitalised (Post, Entry, Order rather than posts, Entries, or All Orders for example)

  • Where a field references another data type or option set (e.g Variant has a field of type Product), name that field the name of the type exactly. So in this case the name should be Product and type also Product. This helps you easily identify what a field refers to and the type of that field.

  • In relation to the above, don’t save foreign types as fields like ‘ProductID’. ‘ProductID’ just implies it’s a string (a text field) when actually it’s a Product.

  • Where a field is basic data (i.e text, yes/no, number etc where it doesn’t reference a type/option), name it lowercase with extra words capitalised. e.g dateOfBirth, or fullName. This helps you know that a certain field is just a basic field rather than referencing other data in the DB.

Other people will have slightly different methods but you get the gist :slight_smile:

5 Likes

Its super simple.
I can guide you how databases works in bubble.
Lets discuss on call.

To add to George’s great reply (FYI, George, I use literally the same naming convention in my apps, which might make you want to change yours for one reason or another :wink: )… and you might already know this, but it’s not clear to me from your post… the only thing defining the primary display fields affects is how data is searched for and displayed on the App data tab in the editor. Defining those display fields has no effect on any functionality within your Bubble app.

1 Like

Thanks for the reply.

So I have now added a field called Variants in my Product DB table and set it as a type ‘List of Variants’ (a single product has multiple variants). But still no joy on the frontend.

Do I actually need to populate that new ‘List of Variants’ field with a list of variant IDs? (seems inefficient to do it that way).

I did not know that, so thanks for clarifying. I assumed it was how you specified the keys.

1 Like

You don’t need to populate it with the IDs themselves, but you do need to populate it with the variant things that are associated with each product. You could also have a product field on the variant data type, and you could populate that field with the product that is associated with each variant. But, one way or the other, an association between a product and its variants needs to be made in the database.

1 Like

To be explicit (because this also comes up a lot)

Let’s suppose you create a Shopify Variant in Step 1 and Shopify Product in Step 2. In Step 2, you want too add the Shopify Variant you created to the Variants list. In the expression composer, this looks like:

Variants add Result of step 1 (i.e, the thing you created in Step 1).

This is different from:

Variants add Result of step 1's unique ID because unique ID is a string, not an object.

To make matters more confusing, in the Bubble database, when you correctly do Variants add Result of step 1, what actually happens is Bubble adds the unique ID of the Shopify Variant to the List of Variants.

It does sound incredibly conceptually strange - there’s the Thing (a data) and the Thing’s unique ID (which is a property of the Thing). However, when you save a Thing you’re saving the unique ID (which is a property of the Thing). Perhaps you could imagine each thing as having two unique IDs - one is the Thing itself, and the other is the uniqueID property of the Thing. The former is a data type (e.g Shopify Variant), and the latter is a string (text).

The more I think about it the more it gives me a headache but you’ll get your head around it :slight_smile: I know you didn’t ask for this information, the whole Thing vs Unique ID thing (ha) just sent me into a crisis. I’m so sorry.

2 Likes

Maybe I picked it up from you somewhere in the forum :laughing:

1 Like