Relationships - what do they actually DO?

[biota]

Hello all

I have been struggling for a while, and I think this is because I am misunderstanding what Relationships can do/are for. 

Ned, in his tutorials, says "...stamp the PK as an FK into the child table", but how does the stamping part  actually happen?

My assumption was that all we have to do is link the tables, and somehow when one of the fields is used in a dropdown or autocomplete, then any other columns would be pulled into the destination table.

I do understand now that any further columns would have to be brought in by either trigger, or autocomplete.

So my question is, what does the Relationship achieve (assuming we are not using a view)? And with views, I am not sure I see the advantage of those, either!

It also seems like if we are pulling a field in by dropdown for example, then that field would already be 'stamped' into the destination table. So again I am not sure what the Relationship is doing here.

TIA

[CoopperBackpack]

Hello @biota,

Just to clarify:
1) Are you referring to the Relationps layout that is available under the Tables (please see the screenshot)? (so, you want to understand the purpose of this layout)

2) Or are you referring to the FK fields like Product_ID and Customer_ID on the Orders_TBL? (so, you want to understand the purpose of having these fields in the so-called child table)

[biota]

Hi @CoopperBackpack, thanks for the reply.

Really, I'd say I mean both!

I've read up a  bit on database normalisation, so the logic there is clear. I also understand how the above layout works.

My questions relates to what added functionally these relationships give us. What  is the point at the end of the day?

When we bring data in from a linked table, we still don't get all the remaining fields from that table, for example. We have to use a trigger for that.

Is the only benefit that dropdowns on the related fields are pre-set, without having to select a lookup? 

Ned talks a lot about 'stamping' values in other tables, but that is really set at the datapage level. So I don't currently see how a relationship makes much difference to stamping these values?

 

[telly]

Hi @biota,

The advantage of using table relationship:
- Display value on the table and DataPages
- Automatic join on the view 
- Able to see what join you are setting up
- Better visualization

And some of the features you can enable on the table relationship like:
- Referential Integrity
- Cascade Update and Delete

For more information you may check these links:
- https://howto.caspio.com/tables-and-views/relationships/relationship-settings/
- https://howto.caspio.com/tables-and-views/relationships/database-relationships/

I hope this helps!

[CoopperBackpack]

Hello @biota,

It is easier to explain the purpose of using the Relationships layout. 

telly has already covered this part.

To sum up:

1)  It can be helpful for better visualization. So, looking at the layout you can quickly see the links between the Tables.

2) The Relationships settings have some options that can be enabled. And these options are available only here. 

3) When the Relationships are added in the Relationships layout and you create a View to join the Tables,  on the 'View Properties' screen, the relationships are built automatically. 

If the Relationships are not set, you need to join manually in the View. 

 

However, even if you skip creating Relationships in the Relationships layout you can still have an app that works fine.
I will comment on Relationships in general in the next post of this thread. 

[CoopperBackpack]

@biotaBut Relationships, in general, are really important since Caspio uses SQL Server which is a relational database.

Relational Database breaks the data into multiple different tables that have Relationships.

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables.

The main benefits of Data Normalization are:

• Better and quicker execution / better performance

             (Imagine that there is a Report built on a huge Table that stores not normalized data, the Search on a Report will take more time than the Search on a normalized Table)

• Reduces redundant data
• To skip so-called side effects such as Insertion anomaly/Update anomaly/Deletion anomaly

              For example, you need to update the customer`s email. This can be done in the Customers_TBL within one record. 
             Imagine, that the Tables are not normalized and all the data is stored in the Orders_TBL, including the Customer`s name, email, phone, etc. In this case, email must be updated in multiple records and some errors can happen. If the update is only partially successful – the customer`s address is updated on some records but not others – then the relation is left in an inconsistent state.

 

Summary: it is crucial to create normalized Tables and to have Parent Keys and Foreign Keys to identify the records in Parent and Child Tables.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Let`s use an example with Customer_TBL, Products_TBL, and Orders_TBL.

Customer_TBL stores unique customers and Products_TBL stores unique products. So, these are so-called Parent tables. And it is an easy task to set up a Parent Key for these tables.

We just need to use a Field with any auto-generated data type (Autonumber, Random ID, etc.).

And usually, we need to configure DataPage to stamp Foreign Keys(Customer_ID and Product_ID) to the Orders_TBL (as you mentioned).

For example, Customer_ID can be taken from the Authentication parameters:

 

And Product_ID can be taken from the Lookup table:

 

Summary: As a result, the Orders_TBL is related to the Customers_TBL and Products_TBL with many-to-many relationships because Foreign Keys are stored in the Orders_TBL.

This way we can understand which product and which customer each order belongs to. 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now, what are the benefits of this table design?

 

Actually, it is possible to manage some use cases only when Tables have relationships.

 

1) For example, you need to create a Report DataPage to display information about the Orders and about the Customers.

To achieve this, we can create a View and join these 2 Tables. And to join them, we definitely need to store Customer_ID in the Orders_TBL because this field is needed to join the Tables.

Tables are joined on Customer_ID:

Report example that is built on this View:
 

 

2) Another example: let`s say a week after the order was created you want to send a post-purchase email.

We can utilize a Task for this use case. To get the customer`s email and to populate the email body with the customer`s name and the name of the purchased product, we need to join all 3 Tables. 

And again to join them we need to have appropriate Foreign Keys in the Orders_TBL.

Email:

3) One more example. Let`s say you need to create a Report with the list of customers. And to open another Report for each customer with the list of orders of this customer.

For that, I can create a Report that is based on the Customers_TBL. In the HTML block, I added a link to a second Report that is based on the OrdersTBL and passed the customer`s ID as a parameter to filter the records in the OrdersTBL that are related to a specific customer. 

HTML block in the Customer`s report to pass the ID of the customer:

Report with the list of customers:

Receiving parameters on the Order`s Report:
 

The Report that is opened when I check orders of John Doe (so, after the 'Check Order' link is clicked for John Doe record):
 

And again it was possible to create a Report like this because the Customer_ID is stored in the Orders_TBL as a foreign key. 

 

I hope I understood your question correctly and this information is helpful.

If not and you still have questions, please update this thread. 

[biota]

On 3/16/2023 at 1:57 PM, CoopperBackpack said:

@biotaBut Relationships, in general, are really important since Caspio uses SQL Server which is a relational database.

Relational Database breaks the data into multiple different tables that have Relationships.

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables.

The main benefits of Data Normalization are:

• Better and quicker execution / better performance

             (Imagine that there is a Report built on a huge Table that stores not normalized data, the Search on a Report will take more time than the Search on a normalized Table)

• Reduces redundant data
• To skip so-called side effects such as Insertion anomaly/Update anomaly/Deletion anomaly

              For example, you need to update the customer`s email. This can be done in the Customers_TBL within one record. 
             Imagine, that the Tables are not normalized and all the data is stored in the Orders_TBL, including the Customer`s name, email, phone, etc. In this case, email must be updated in multiple records and some errors can happen. If the update is only partially successful – the customer`s address is updated on some records but not others – then the relation is left in an inconsistent state.

 

Summary: it is crucial to create normalized Tables and to have Parent Keys and Foreign Keys to identify the records in Parent and Child Tables.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Let`s use an example with Customer_TBL, Products_TBL, and Orders_TBL.

Customer_TBL stores unique customers and Products_TBL stores unique products. So, these are so-called Parent tables. And it is an easy task to set up a Parent Key for these tables.

We just need to use a Field with any auto-generated data type (Autonumber, Random ID, etc.).

And usually, we need to configure DataPage to stamp Foreign Keys(Customer_ID and Product_ID) to the Orders_TBL (as you mentioned).

For example, Customer_ID can be taken from the Authentication parameters:

 

And Product_ID can be taken from the Lookup table:

 

Summary: As a result, the Orders_TBL is related to the Customers_TBL and Products_TBL with many-to-many relationships because Foreign Keys are stored in the Orders_TBL.

This way we can understand which product and which customer each order belongs to. 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Now, what are the benefits of this table design?

 

Actually, it is possible to manage some use cases only when Tables have relationships.

 

1) For example, you need to create a Report DataPage to display information about the Orders and about the Customers.

To achieve this, we can create a View and join these 2 Tables. And to join them, we definitely need to store Customer_ID in the Orders_TBL because this field is needed to join the Tables.

Tables are joined on Customer_ID:

Report example that is built on this View:
 

 

2) Another example: let`s say a week after the order was created you want to send a post-purchase email.

We can utilize a Task for this use case. To get the customer`s email and to populate the email body with the customer`s name and the name of the purchased product, we need to join all 3 Tables. 

And again to join them we need to have appropriate Foreign Keys in the Orders_TBL.

Email:

3) One more example. Let`s say you need to create a Report with the list of customers. And to open another Report for each customer with the list of orders of this customer.

For that, I can create a Report that is based on the Customers_TBL. In the HTML block, I added a link to a second Report that is based on the OrdersTBL and passed the customer`s ID as a parameter to filter the records in the OrdersTBL that are related to a specific customer. 

HTML block in the Customer`s report to pass the ID of the customer:

Report with the list of customers:

Receiving parameters on the Order`s Report:
 

The Report that is opened when I check orders of John Doe (so, after the 'Check Order' link is clicked for John Doe record):
 

And again it was possible to create a Report like this because the Customer_ID is stored in the Orders_TBL as a foreign key. 

 

I hope I understood your question correctly and this information is helpful.

If not and you still have questions, please update this thread. 

Wow, thanks for all your work here @CoopperBackpack! That's really clarified things for me

[Queso]

Hi @biota,

You can check the following recorded live stream for more information about creating One-to-Many Relationships With Subforms: https://www.youtube.com/watch?v=Mud33bhZbDc

[biota]

On 3/18/2023 at 1:10 PM, Queso said:

Hi @biota,

You can check the following recorded live stream for more information about creating One-to-Many Relationships With Subforms: https://www.youtube.com/watch?v=Mud33bhZbDc

Thanks. Useful vid!