Setting up Data Relationships

Need help Database
1

I have 3 different tables and the tables are related to each other by a unique ID from an external system that I’m extracting the data from.

Tables
Table 1 - Company (External Company ID)
Table 2 - Persons who work at the company (External Person ID, External Company ID)
Table 3 - Events (External Company ID, External Person ID, External Event ID)

Below are the relationships :
1 company has more than 1 user.
1 company has multiple events.
1 event is tagged to 1 company and 1 user.

How should the fields be set up to ensure the relationships still remain that way? And how do i use the bubble unique id to build the same relationship unless it’s okay to still use those external IDs as a primary key in Bubble.

2

Screenshot 2024-01-23 203951
Screenshot 2024-01-23 2039511034×512 34.4 KB

3

Hello @bkay ,

Seems like you are developer who understands database normaliztion. I came in the same way. It is pretty straightforward but there is a bit of a learning curve.

Bubble calls a row a thing and each thing has a datatype. Data types are the table definitions. Bubble uses its own unique ID field (read GUID) as the primary key. The good thing about that is when you create the Person data type you can create a column of type Company. Bubble will automatically populate this field with the above unique ID.

The interface will allow you to select the types and use different input types to make the selections. Bubble will take care of the IDs.

4

Hi @doug.burden

Thanks for your help! I did the same thing and when i imported the data in CSV, I have to map it to at least one of the fields.

Like one of Type_1’s fields have to be mapped to the data in Type_2 table. And I did the mapping using external IDs.

Does that mean the relationship has already been created?

5

Hi @richard.binnington

This is so helpful and thank you for confirming as I needed to know whether my logic was correct.

image
image923×313 13.9 KB

I’ve followed the steps to do that but is that already enough to build the relationship between one table and another using external IDs and I don’t need to use Bubble’s unique ID to create relationships again? :smiley:

6

Sounds like your scenario is a bit more complicated than I first thought. If you are importing CSVs with the IDs in them there will need to be a transformation step to

You may want to set up a staging table to hold the imported data and then use a workflow to transform the data. You would create a new thing for each row and do searches for the companies based on the External ID.