Video: A Walk Through on How to Create Database Relationships in Caspio

A well-designed database should be able to provide accurate information on-demand, especially if your data is subjected to frequent changes or updates. For this reason, carefully organizing data as structured content in your database is a critical process. One way to do this is through data normalization.

Data normalization is a process by which large tables are divided into smaller tables, and then relationships are defined between them. These relationships could be one-to-one, one-to-many, or many-to-many. The idea behind normalization is to eliminate redundant information and avoid data anomalies that could compromise the integrity of your data. Additionally, you can reduce the amount of space your database consumes and cut the need for tedious maintenance processes if your database is normalized.

But in order to normalize your database more effectively, you need to understand how each database relationship works. Let’s take customers’ data and their orders as an example. Prior to normalization, customer’s data is repeated for every order he or she makes.

Figure 1. Customer Orders Data Before Normalization

To normalize, separate the data of the customers from the orders into two different tables, a “parent table” (in this case, the customer data) and a “child table” (in this case, the orders data). Then, you can use a primary ID in the parent table and a corresponding foreign ID in the child table to form a relationship between the two tables.

Figure 2. Normalized Customer Orders Data

Once you’ve configured relationships between interconnected tables, you can work with them as if they were a single table.

To better understand database relationships, here’s a three-part video series that guides you on how you can create table relationships in Caspio.

Part 1: Database Relationships – Data Normalization
This first video talks about the concept of data normalization and guides you how large tables can be divided into smaller tables.

Part 2: Database Relationships – One-to-Many Relationships

In this second video, we walk you through creating one-to-many relationships in Caspio.

The example discussed above on Customers table and Orders table is a one-to-many relationship. As each customer places multiple orders, each order only belongs to one customer. When creating a relationship between two tables, if only one of the related fields is unique, a one-to-many relationship is automatically set.

Watch the video below to learn how to link records in a parent table to one or more records in the child table while the records in the child table only relate to one record in the parent table:

This last video discusses how to create many-to-many relationships. This type of database relationship happens when each record in the parent table relates to multiple records in the child table and vice versa.

Following our earlier example, the relationship between the Products table and Orders table can be considered a many-to-many (relationship). Each product can appear in multiple orders, and one order can have many products. Take a look at a quick illustration of this type of database relationship in the video below:

You can learn more about database relationships in our Online Help Center. If you don’t have a Caspio account and you want to explore how to build custom online databases and apps for your business, sign up for a free account or request a no-obligation project consultation with one of our product experts.