Pretty new to Bubble. Just curious if it is possible to develop some sort of a relational database with Bubble?
With one to many, many to many…etc?
Thank you
You can do a lot with Bubble that is comparable to relational databases. I would not, however, call it a relational database.
You can, for instance, store one to many relationships by storing references from one table (Bubble data type) to the other in each table. As an example, if you want to have one to many relationship of Company to Person, you can store in Company a field of type Person that is a list, so it holds multiple Persons. By the same token, in the Person table, you can have a Company field that stores one Company reference.
However, there is no equivalent to SQL language for easily extracting related data, e.g. there is no select or join syntax. You can accomplish some of what you want, but the language is not at all the same. Furthermore, with no equivalent of SQL update syntax, updating data can be a challenge.
I hope this is somewhat helpful.
3 Likes
Thank you very much for the quick and informative response.
I will give it a try and see how far I could go with it.
1 Like
I think it depends on what you are really asking.
Is the Bubble database based on a relational model ? Well, yes. It has tuples, data types, attributes and relations. And by that I mean relations as in tables, not relations as in relationships between tables.
Does the Bubble database look like a relational database management system ? Well, up to a point (see above), but you won’t find keys, either primary or foreign. Bubble handles that in a more “object database” way by embedding one record in another (virtually, whether it does it physically is another matter) as described above.
Does the Bubble database obey Codd’s 12 rules ? Hell no. But then not much does.
Does Bubble support SQL ? No, but then it certainly isn’t NoSQL either.
Can you implement a complex table structure with relationships such as one-to-many, many-to-many, association classes, a table with one to many relations on itself … yes.
What you can’t do is “tune” the queries in the same way you can with SQL/mySQL so no explains, no adding indexes etc.
3 Likes
Thanks Nigel, will dabble with for a while see how far I could go.
@laurence in this post, is what you are saying about a field that is a list of people, that i can store a list in a field?
So for example if i am storing an order from a customer, the order field could have an entry with several items that the client ordered, rather than just one entry?
I am trying to store order histories, and am unsure whether i need a new column for each item the client orders, like item1, item2, item3 and so forth. Or whether i can say “items ordered” as a field and then have one cell with all the items in just one entry.
Am I making sense?
As a simple example, use three custom datatypes: Customer, Order and LineItem.
Customer will have an Orders field. The Orders field will have field type Order (the custom datatype.) Because a Customer will have multiple Orders, the Orders field will have “This field is a list” checked.
Similarly, the Order type will have an Items field of the LineItem type with “This field is a list” checked.
This way, you can have multiple LineItems in an Order and multiple Orders for a Customer.
This example is overly simplified. For LineItems, you’ll want additional fields like QtyOrdered, QtyShipped, DateShipped, etc. You may need a way to split a line if not all items are shipped at once. The complexity increases with the sophistication of business needs.
Give it a try. Hopefully, someone else can point you some tutorials on the topic. My learning is from nearly thirty years ago, just recently being applied in the Bubble environment.
Good luck.
Hi, I am struggling with similar example, like you mentioned.
The struggle is when I want to reference particular order which is a type of order.
I wanted to create “Currently processed order” filed in User, which would be type of order and use it as a pointer - an Order_Number and set it simply by +1 (just an example). I am receiving an error.
Another example is to have a Book, where are Chapters and Pages/Paragraphs and Current User has a bookmark where he finished reading, so that he can continue reading in his/her next session.
How do you approach this, please?
You can try something like this to get started. It works, just keep in mind that if two orders are created at the same moment, you’ll run into duplicates so you’ll want to add logic to minimize that.
EDIT: I’m going to add that I think a better way to utilize an order number, would be to use the built-in unique Id value of the order thing, which then removes any concern for dupes. Now, this likely brings up concerns with how long that string is if presenting to a user. In that case, i would take a certain number of characters from the unique Id and concatenate that with a time string. Anyhow, there are options to meet your specific needs.
Thank you, this looks good. I will definitely use the automated numbering function.
What you described should work because as a result from step 1 I really get something of type Order.
What if I want to create just randomly the pointer currenty.processed.order may be just using Manager action.
I would like to set currently.processed.order manually (or may be better example is the book bookmark) but I don’t know how to reference the currently.processed.order (type of order). Directly in database I can input some order.number but I am not able to fill the value using workflow 
I am actually trying to input initial value to start with, because I cannot input default value in the database field when it is Type of Order (or list of orders).
Do you have any experience with this please?
Not sure I’m following. 
And it feels like we’ve left the topic of the original thread. My suggestion is to start a new topic, and focus on one particular issue. Describe what you are trying to achieve but also post screenshots of how you are trying to solve the problem. This can help us all visualize with you.
Thank you very much, so I tried to create new topic.
Quick question Laurence to help me better understand the concept of handling data in bubbles: Does Orders “Data Type” (which I guess is equivalent of a Table) also contain a User field? Or does it only exist as a sub-record to the User?
Hope my question makes sense.
Tom,
I was describing a hypothetical situation and I’m not sure it relates to what you might want to do.
Firstly, you are correct that a Data Type corresponds to a table. Data Type is the description of what is contained in every “record” in a table whos type is the Data Type. (In Bubble, any “record” is referred to as a “thing”, although I rarely see that term used in forum discussions.)
Continuing with the Order example, it would have a User field if User is meaningful to the order. That depends on your use cases. If a User is equivalent to a customer, then I would definitely have User as a field in the Order type.
1 Like