In this article, we will explain how to organize your data efficiently using a relational approach, using a hypothetical dog walking app example.
Understanding Relational Data
In a relational data structure, information is organized into separate tables that are related to each other. Each table represents a specific type of data, such as Products or Orders, and contains relevant information about that particular entity.
Click here to read the full article on Relational Database Theory
How To Use Relational Data In Frontly
Consider the following data sources below. We have an 'Orders' table and a 'Products' one and we want to establish a relationship between them in some way.
In this instance, the goal is to connect each individual product from the 'Products' table to their corresponding order listed in the 'Orders' table.
Orders
Products
Setting Up Relational Data
After importing your data sources, click ' Add Relation' in the Data Relations tab of your 'Data Sources' settings to pull up the setup module.
A breakdown of each setup field is as followed below:
Sheets (Parent - Child)
In this example, I establish my 'Orders' sheet as the Parent Sheet, with the 'Products' sheet as the Child Sheet. This order is important because of the way this data is related. We want to be able to get an in-line view of the order details for each product listed. Note: Though the set headers mentions 'Sheets', the data relation setup is enabled for Supabase data tables as well.
Columns
Once you've selected your Data Source with the primary table first and the secondary table next, you will select the Fields that relate these two tables.
From these data sources, each product in the 'Products' table has a value in the 'order_id' field. This value will line up with the corresponding 'frontly_id' value in the 'Orders' table.
Display Name
Finally, the Display Name dropdown determines which field values are used as the primary display when referenced in a form. Since the related field values are typically an overlapping ID value, configuring this replaces that ID with values that are more relevant to your users. More on this in the next section.
Upon the completion of this setup module, hit save and see the results!
Step 2: Add a Table to a page
Recall how the 'Orders' data was displayed on its own prior to the relational setup. After making this connection and creating a table block on a page, you can now see how the parent sheet's dataset is stitched into each row, based on the overlapping values of the columns you've configured.
Fields on this table can be cleaned up and toggled on or off depending on your display preferences. The connecting field for instance, can likely be hidden.
Related fields can still be renamed as well to keep their labels more simple.
Now, the table looks like this. Much cleaner!
Step 3: Using Related Values in a Form
A common use case of using relational data in a Form block is through a table's detail view.
Note the examples below. Rather than having the clicked record's raw ID value displayed, it can instead be substituted by the corresponding field value of your choice through the selection of the 'Display Name' field in your relational data setup.
Without relational setup:
With relational setup:
This is how you set up relational data in Frontly! There are unlimited applications for one-to-many relationships like this. Try setting up one today and reach out to our Support team if you have any questions!