Data Structure Relationships

Overview 

Managing data within Decisions, or any other database, requires an understanding of how to build relationships between different sources of data. This article will discuss the importance of database relationships, the three kinds of database relationships, and how to build relationships within Decisions.

The Importance of Database Relationships

Relationships connect two tables containing information together. The main benefit is to be able to build reports that compare different groups of information. The other important benefit is that it allows simplified and specialized tables to house information. Take, for instance, the employees at a company. There's many different kinds of information that an employee represents. Each employee has a name, contact information, salary, years of service, etc. Putting all that information into one table might seem to make logical sense, but what it creates is unwieldly to read, and difficult to update.

For more information on database principles and good design go here: Database design basics.

Building Database Relationships within Decisions

There are two ways to build relationships. The first is shown below where two tables contain a matching field. Decisions automatically builds relationship connections between Data Structures as long as the Name and Type are the same and the Data Structures are in the same project folder. The second way is to use composite data types. One Data Structure can be added to another as a data type, as seen below. The composite data type is also necessary to use Decisions' Report feature since only one Data Source can be selected at a time.

One-To-One Relationship

A One-To-One Relationship connects a single point of data between two tables. This can be built in Decisions by using one common data field.

This would allow a user to run a query for EmployeeId and return information from either or both tables. An employee only has one record for the Id field and only one HireDate record which is why it is called a One-to-One Relationship.

One-To-Many (or Many-To-One) Relationship

In a One-To-Many relationship a single record is used across multiple records. This can be built in Decisions by using one common data field. 

An employee still only has one Employee Id record, but it is possible for them to have multiple records for Task because they could be assigned many different tasks. Using the EmployeeId field on a report will return all of the TaskId for that Id giving the user all the data for a particular employee. The relationship also works in reverse. If a report pulled every TaskId it could show the user every employee that had been assigned a ticket. This is why the relationship can also be called Many-To-One.

Many-To-Many Relationship

Many-To-Many relationships occur when multiple records are shared between multiple tables. This kind of relationship does not provide good results because there are too many possibilities for how the data can be returned or there is no way to make a logical connection between two tables.

For this example here is a table with customers and products. There are many customer and there are many products, but the relationship issue is that a customer can buy many products and a product can be bought by many customers. 

There are two ways to solve this in Decisions.

The first way is a Join Table. Use an intermediary table to make a connection. All that is necessary is that the Join Table has a Primary Key field from both the tables that need to be linked.  In this example a logical way to set the table up is to consider the data as an order. A customer makes an order for a product and the connection is made between the two data structures. 

The second is to create a composite type. Using this method involves putting a data field from one data structure into another as a data type creating a Parent Structure and a Child Structure. The Parent Structure will have all of the data fields from the Child Structure. To take advantage of Decision's reporting capabilities the composite type is required.

Manual or ORM?

Users may wonder which method of relationship building is best for them. Below is a chart with the strengths and weaknesses of both approaches.

Viewing Relationships

There is not a dedicated dashboard or other mechanism that displays data structure relationships in a diagram.

Saving and Creating Data

After a relationship has been built data can be saved to each data structure in the relationship without concern. Relationships do not affect the steps that are used to save data to a data structure. Being a Parent or a Child data structure has no impact on the steps that are used to save data to those structures.

Creating new fields within a Parent or Child data structure will not impact the relationship. This is also true for Many-To-Many relationships.

The only data change that can break a relationship is to remove the field that binds the structures together. For Many-To-Many relationships the link between them can be broken either by removing the binding field, or by renaming it.

Data Extensions

Data Extensions can be used within Flows to create variables that mimic fields in any database structure. Using them can cut down on the number of steps used to fetch entities. 

For help simplifying the design time experience for manually created relationships or Many-To-Many relationships, please review the Data Extensions article.