Building Cascading Connections

In this episode, we'll discuss building a series of Data Tables with a defined hierarchy where each table contains direct connections to every table above it.

Features Discussed:

1. Cascading Connections (Time: 3:00)
   Learn about cascading connections and how to set them up for efficient data management.
2. Connection Fields (Time: 8:30)
   Learn how to properly structure relationships when working with five tiers, such as companies, projects, deliverables, and time tracking.
3. Table Rules (Time: 14:16)
   Learn how to apply rules after a record is created or edited, and then automatically enforce these rules.
4. Connected to Page (Time: 21:09)
   Learn how to display a table component on connected pages, enabling you to show related data seamlessly across different sections of your application.
5. Options (Time: 26:33)
   Learn how to set an "Add New Record" form to display in a pop-up window.
6. Card Component (Time: 31:31)
   Learn how to use the Card component to display calculations and implement formulas (e.g., sum) within the card. Example: Dynamically show totals or averages to provide key metrics at a glance.

 

---

Transcript Summary

Introduction

Hey everybody, and welcome back to another episode of Build It with Tadabase. On today's episode, we're talking about connection fields, specifically cascading connection data structures—a series of tables that all connect to each other from the top-most level table or object all the way down to the lowest level object.

This is a kind of structure that is typically seen inside of the database platform and provides a very clear and defined hierarchy of tables. It also helps define your page structure. We're going to go in-depth on how to set this up with the data builder so that you can give yourself the best possible chance for success in the page builder. We'll be talking a lot about connection fields, rules, and best practices when building in a database.

Let's get started! We'll see you in the builder.

Finished Product Overview

All right, so here's our finished product that we're working on today. Most of the content here is going to be done inside the data builder—the actual data structure and how the connections are organized. The way the connections are organized is that every single table has a direct connection to the tables above it.

What I mean by that is this application or this group of tables has a very clear and defined hierarchy. Companies are the top-level item, projects are the next level, and so on until you get to time tracking, which is the lowest level item. Time tracking has direct connections to everything above it: the task, the deliverable, the project, and the companies.

When I'm looking at a particular task, and I add a time tracking record, because we have direct connections throughout this whole system using various automations like table rules or form rules, you do not have to set any connections here. We're utilizing the actual connection source in the page and table rules to automatically set all of the cascading connections from the bottom all the way up to the top.

Additionally, this gives you the ability to perform dynamic calculations at any given level, whether it's the company's table, the projects table, deliverables, or whatever table. We can always perform calculations on everything below it, like how many projects, how many deliverables, how many tasks, and the total time worked company-wide.

That's what we're doing today. We'll show you how to build it now inside the builder.

Cascading Connections Setup

As I mentioned in the intro, today we're talking about cascading connections and setting up connection fields in a way that you have a series of connected tables, where you have a parent-most table and then, at the bottom, a child-most table or the lowest level table in the series of tables.

This could be one small section of your application, or it could be the content for your entire application—it's entirely up to you. What we're focusing on here is grouping this thought together into a manageable thing.

Example: Project Management

We're going back to an old favorite—project management—which I think is easy for a lot of people to understand. That's the example we're using today.

Looking at the image in front of us, this is a basic example of how the data structure will look. Each object here is a table. We have a companies table, which is the top-level object. Everything connects back to a company. The first column shows all our actual tables: companies, projects, deliverables, tasks, and time tracking.

Each data table always connects back to the most relevant or direct parent. Projects always connect back to companies. As we keep going down, we continue that process of always connecting to the previous tables. Projects connect to companies, deliverables connect to the first two tables, tasks connect to the first three tables, and time tracking connects to the first four tables.

Building the Data Structure

When talking about connection fields inside the database, there's a certain theory, technique, or best practice that we should always keep in mind when building out these tables and connection fields. That's the direction of the actual connection.

Ninety-nine percent of the time, you're going to have connections that go from a child to a parent. This is a good way to describe the actual relationship because it's easy to understand the relationship between a child and a parent, which translates well into connection fields and relationships between tables.

However, there are other ways to think about or visualize this. The language I always like to use, not only in my head when I'm thinking about how to build something but also when I describe it to other people, is "what belongs to what?" A project belongs to a company; a company does not belong to the project. If we think about it that way, the connection field will live on the project level.

Another way to think about this is from smallest to largest. Time tracking is the smallest entity within a project, and it always belongs to the larger object. Lastly, another good way to visualize connections is individual items that connect to a group of things, like products and product categories, where the product is the individual and the product category is the group.

Let's switch over to the builder and start building out these tables. As we build this out, we'll talk about why this structure is so important. We'll not only set up the data builder, but we'll also set up some pages in the page builder.

Creating Tables

First, we'll create the companies table. I'm going to keep this as basic as possible, focusing on the connection fields and some additional text fields, number fields, and count fields.

Next, we'll create the projects table with a connection to the companies table. I like to build the tables in the order that makes the most logical sense, where companies are the top-level item, and time tracking is the lowest level item. This hierarchy is important.

We'll continue with deliverables, tasks, and time tracking, connecting each to the relevant tables above them.

Connection Fields

Now, for the time tracking table, we're going to have connections to tasks, deliverables, projects, and companies. We'll also add a number field for "hours worked."

For tasks, we'll add an option field for "task status" with options for "open" and "closed." This is all we need for now.

When you look at the graph, you'll see that every single table has a direct connection back to a company. This becomes really important in the page builder because everything has a connection to the parent-most object, allowing for calculations and ensuring users have access to all the information below companies.

Table Rules

Let's focus on table rules. We're going to automate some of the connection settings so that we don't have to constantly think about it. If you don't have access to table rules, you can do the same thing with form rules. I prefer table rules because they apply universally across all forms.

For example, when a new time tracking record is created, and it has a connection to a task, we can use table rules to automatically set the connected deliverable, project, and company.

We'll set similar rules for the other tables, ensuring that the connections are automatically set.

Page Builder

Now, moving on to the page builder. For this type of system, it's always a good idea to build out parent pages for every single table.

We'll create pages for companies, projects, deliverables, tasks, and time tracking. These will show data directly from their relevant data tables.

Navigation Structure

When building this page structure, we're going to navigate laterally rather than vertically. This means that when we're on a company's details page and viewing connected projects, we can navigate directly to project details to view more information about that project.

We'll do the same for deliverables, tasks, and time tracking, adding connected page links to move between these details pages.

By navigating laterally, we avoid creating a gigantic nested page structure, which would make the breadcrumb trail unnecessarily long.

Adding Records

Let's add some records to our tables. We'll start with companies, adding a new company called "Database." Next, we'll add a project called "Build It with Tim" and connect it to the company.

The table rules will automatically set the connections for deliverables, tasks, and time tracking as we add new records.

For example, we'll create a deliverable called "Upload Video," connect it to the project, and the table rules will automatically set the company connection.

We'll add tasks like "Create Video Thumbnail," "Record Video," and "Edit Video," all connected to the deliverable. Finally, we'll add time tracking records for each task.

Using Detail Pages

Let's navigate through the details pages. Pay attention to the breadcrumb trail at the top of the page. We're shifting laterally through the application, moving from company details to project details, then to deliverable details, task details, and time tracking details.

This lateral navigation keeps the page structure clean and easy to follow.

Automating Connections

When we add a new project on the company details page, the company connection is automatically set. This is handled by the database internally, so we don't need to manually set it every time.

We'll go back to the page builder and enable the "Add New Record" pop-up for all detail pages, ensuring that connections are automatically set for new records.

Calculations

One of the benefits of this structure is the ability to perform calculations on any table. For example, on the company details page, we can add a card component to count the number of projects, deliverables, tasks, and total time worked for the entire company.

These calculations can be displayed on the page and used in other database functions, such as conditions or filters.

Conclusion

I think that's it for this week's episode. I hope you enjoyed it and learned how to build a cascading connection system where every single table has a direct connection to all of its parents. We also covered how to build a page system that navigates laterally instead of vertically, using connected page links.