Forum Academy Marketplace Showcase Pricing Features

Understanding Relational Database Nature of Bubble

I am struggling to figure out how to relate two tables of data (pardon my terminology, have not quite learned the Bubble lingo works when it comes to data).

I am basically trying to relate and report on two levels of data. One table will contain Projects and another Time Slip (hours logged working on the project). I want to be able to report on the data with a repeating group that lists each project and the SUM of hours logged to that project. I guess I am asking two questions: How to set up the data and How to report on it with a repeating group.

Primarily though, I am trying to understand how to relate the time slips table (Type?) to the Projects table. Reading over a few posts, I may have a more traditional database hierarchical view and need to lose that view work in Bubble. Any help would be appreciated. Feel free to ask and followup questions if my question is not clear enough. Thanks in advance for the help.

Can you recreate (or copy/paste) it in the forum app.

1 Like

I think there are two different ways you could accomplish.

  1. Create relational database with primary/foreign keys that are maintained through workflow

  2. Create a list field in your Projects data type
    2.1 Create Project data type
    2.2 Create Time Slip data type
    2.3 Create new field in Project data type
    2.3.1 Field Name = “Time Slips”
    2.3.1 Field Type = Time Slip
    2.3.1 This field is a list = checked

Now you will have a list of time slips related to a project.

Then in your work workflow you can use the “Make changes to a list of thing” to add or remove Time Slips from the list.

There is also a lesson available to help you understand Lists.

Go to Documentation – https://bubble.io/documentation
Select Lessons

Look for the lesson “Defining a field as a list of things”

2 Likes
  1. I may need to read up elsewhere about the primary/foreign key method…but that may be an option

  2. I went back and worked the lesson on lists. It was helpful, but I am still fumbling somewhere here. Here is what I did so far:

    1. I created the two datatypes: Projects and Time Slips.
    2. I added a “Time Slip” field to the Projects data type and checked the box for “list”.
    3. I added two fields to my Time Slips data type: Project Number (to tie to specific project) and Time Slip (to record the hours).
    4. I created a simple form to input a new record or “thing” into the data type Time Slips.
    5. I created a repeating group that displays the individual time slip entries.

This is where I am lost now…how do I have the repeating group summarize the total hours per project? Because I may end up entering 10 different time slips for the same project. I think I am just summarizing data from the Time Slips data type and not really “tying” the slips to a specific project in the Project data type?

Here is a link to the app so far https://parentchildexample.bubbleapps.io/version-test/index?debug_mode=true , any more help would be greatly appreciated. Thanks.

Fundamentally, this is the bit that makes Bubble (and others) a bit different to a traditional relational DB. You don’t need to do it. Yes, you can set up something that looks like a foreign key, but it makes it much much more complex.

The way you “tie” A Project to many Timeslips, and a Timeslip to One Project is by embedding a particular thing in the database record.

So the TimeSlip has a field of Type ProjectData, that gets set to a particular Project. Not the key or an Id, the whole thing.

So when I add my hours, I have a dropdown of projects.

When one is selected, this dropdown’s value is the whole project thing (object, record, row … call it what you want).

So I can save it my to my TimeSlip.

Bubble is handling all that “Foreign Key” stuff for you, you don’t need to worry about how it works.

This means instead of doing Joins and stuff like that, you can use natural English to query your data.

Imagine we have a Project Manager on the Project, and we stored a User type on our Project.

If we are showing a TimeSlip, and we want to show the Name of the Project Manager.,…we can say …

This Timeslip’s Project’s Project Manager’s Name.

So we can navigate up a set of many to one relationships using just an apostrophe. Cool :slight_smile:

What about the other way round, and here we finally get to your question !

Yes, we could probably sum up all TimeSlips for every unique project, but let’s make like easier for ourselves when querying the database, by making life a little more complex when we save the data.

So I add a field to the Project called Timeslips, and make it a list.

When I save the Timeslip, I also make a change to the Project to store this Timeslip’s thing on the Project in the List.

This adds the Timeslip “thing” you just created to the list of Timeslips in the Timeslip field on Project.

This means that to do the summary of the projects, you can just go down from Project > Timeslips using this relationship.

In my repeating group, I simply list out all Projects.

And then “SUM of hours logged to that project” as you oringinally wrote can simply be added as a text field.

Almost exactly as you wrote it :slight_smile:

Yes, Bubble’s data can get more complex, but in most cases if your data query looks fairly neat and English like … your data is in good shape.

Rather that worrying about keys and normalisation of data … store your data to make it easy for YOU to retrieve.

Example working here …

15 Likes

very good explanation Nigel. Thank you!

But one question maybe:

Lets say I have a thing called Project with a lot of text fields, picture fields, etc.

  1. I could now (for example) create 10 fields for pictures under the thing Project (Image1, Image2, Image3, etc)

  2. OR I could create the thing Project and the thing “Pictures” and then create the field “list of pictures” under the thing project.

  1. Regarding speed, what do you think is faster? Or does it make any difference at all?

  2. Lets say I have a thing with 100 fields under that thing. And sometimes the user will fill out 100 of those 100 fields. And another time he just fills out 10 of these things. Would it make any difference regarding speed? Should I (if possible) split the 100 fields between several things? So that the thing with 100 fields (of which only 10 are filled out, 90 are “empty”) will not be send around all the time eventhough only 10 are filled out? Or is the bubble DB “intelligent” and will “downsize” the thing to only the fields which are filled out/not empty?

I hope my questions are understandable. Sorry for my bad english.Would be great if you could share some of your experience regaridng this topic.

3 Likes

NigelG and Kfawcett… thank you for your explanations. It is very helpful and gets me back on track.

Actually one more question:

What if I have existing data (CSV file) that I need to import. Is there a way to import the CSV of the timeslips in so that they are included in both the timeslips and Projects data type? Do I need to upload it to the Timeslips data set first and then run something within bubble to get each of the timeslip things into the Project data?

Got it to work. Found this post in the forum that answered it pretty well.

Imported data into linked tables?

1 Like

Thank you for nice example. now it s clear to me also :slight_smile:

Yes, that is still the way to do it. Upload the Projects. Upload the timeslips, linking them to projects by matching on a field. Then run a Bulk API.

The issue I had with the Bulk API timing out is now fixed. It is slow at scale, but works.

Hi, This is from an old question…thou would be very useful to get feedback here? Thanks.