Forum Documentation Showcase Pricing Learn more

Data structure : Data model

Hello,

My question is very simple. Does the bubble data model is a relational model (like a sql model) ?

If yes, how can I put in relation two data types (with foreign keys) ?

If not, how can you do it to create an efficient data model ?

Thanks

1 Like

It is probably more NoSQL than SQL.

You create your “tables” and then embed these in each other by creating the type as your “table”.

I like the fact you don’t have to define these structures in advance.

I don’t really anderstand. Let’s use an exemple :
Imagine I need to create a Supplier and I need to create a Client into my app. And you can have 1 or several Clients per Supplier but I need to let the possibility to declare witch Clients belong to witch Suppliers.

With a SQL model, I would have used 3 tables : 1 for the clients, 1 for the supplier and 1 more that links the 2 tables to declare witch belong to each other using foreign keys of the 2 others tables.

How can I do that into Bubble Data Model ?

You create two tables in Bubble, one for Supplier. One for Client.

In Supplier you create a field called (for example) ListOfClients and set it to be type Client and say it is a list.

In Client you create a field called (for example) ListOfSUppliers and set it to be type Supplier and say it is a list.

Then you can do operations on this list, so add/ remove a new Client, add a list of Clients etc etc

It is very powerful when you get your head round it.

1 Like

I come from the SQL world myself @Jackfaith and believe me, I know what you are going through! :wink:

Specially when you realize that you don’t even need to setup relationships of any kind with bubble if all you need is a ONE-TO-MANY relationship! That is, a Supplier can have many Clients, but a Client can only have one Supplier.

All you really need is to make sure your CLIENT and SUPPLIER tables both have a unique SupplierID.

Now anywhere in your app, when you need to reference all the client’s that belong to a particular supplier, you can just do a search for all clients and add a new constraint: Client.SupplierID = Supplier.SupplierID (assuming you have a particular Supplier chosen)

BUT…

Coming from SQL world and seeing the given ability to create relationships like @NigelG demonstrated above without having to worry about indexes, I have created these relationships for all my tables. Now, in the SQL world this makes your search faster by creating indexes on these fields. Also, it adds extra security since it prevents the user from deleting a client if it is linked to at least one supplier.

But what about in the Bubble world? I still don’t know if we get better performance and protection/security if we do use the relationship method in Bubble rather than just use a common field approach. Does Bubble gives an error when trying to delete a Client linked to a Supplier?

Maybe @georgeciobanu or @emmanuel can better clarify why having the ability to reference a list of client’s from a supplier using the type element is better than just using the constraint approach (besides the obvious: "saving the extra step of creating the constraint every time).

Can you describe a situation you’d like to build in Bubble? that’d be easier than a general description from us.

OK,

In my application, i have “industries”. I also have “production lines”. Each industries can have from 1 to many “production lines” and each “production lines” can have from 1 to many “Industries”.

The purpose is to link “industries” and “production lines”.

It gives :
One data Type “Production lines”
One data Type “Industries”
One date Type “Production lines declared by industries”

My question is : Is it possible to use Foreign Key and link “production line” and “industries” to make my 3rd data type ?

So basycally, the question is how do you manage to solve the relation many to many :smile:

There are several ways of doing this, and it really depends on what questions you want the data to answer.

Either denormalise it all, and have a list of Production Lines on the Industry, and a list of Industries on the Production Line.
i.e. you embed the related things in each. Then you can query both ways as you don’t have to implement the many-to-many. You implement both the one to manys in the one.

You don’t have to embed these in both directions, it will depend on how balanced the data is. Embeding a single thing many thousands of times might not be ideal. You can link it preogrammatically if needed.

The other is to resolve it in another linking thing as you have done above, which is how you would do it in a relational database.

As to what is “best” … that can only be answered by what questions you are answering. I would suggest the former will be a lot simpler to write queries for.

1 Like

You can define a field in the production type that is of type industries, and vice and versa. The field could also be a list of product lines for the type industries (for instance).

Then, you can say something like, in a repeating group"current cell’s production line’s list of industries: first item". Does that make sense?