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
Step 1. 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:
Parent - Child
In this example, I establish my 'Orders' data source as the Parent, with the 'Products' data source as the Child. 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.
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 1.1: Establishing Data Relation in Supabase (If Applied)
If you're using Supabase as your primary database and you're creating a data relation with tables from there, you'll need to configure the data relation within the Supabase platform as well.
Using the same Orders <> Products example above, one way to establish this relationship in Supabase is by accessing the settings of the related child field (Products > Order ID) and creating a Foreign Key.
The setup of the foreign foreign key fields should look identical to its configuration in Frontly. See below.
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!