Understanding custom table relationships

What are tables?

A table is a data structure that organizes information into columns and rows to resemble a spreadsheet. A table can be used to both store and display data in a structured format. Further,

1.  A table has a set number of columns, but can have virtually unlimited rows.

2.  Each row in a table is called a record and each record has a unique identifier. 

3.  That identifier is called a key. A key can be a single field or combination of fields. 

4.  A key is used to link records to data stored in other tables.

5.  When records in one table are associated with records from another, a data relationship is created.

Sometimes, our customers need to use tables to serve as extended storage for Contact information, or to store multiple records of information per email address, or to connect data not stored on a contact record directly. For these purposes, Delivra offers custom tables.

How are tables used?

Custom table functionality can solve the following common data needs: 

1.  More Contact fields
A custom table can be utilized when a customer needs more contact fields, or more of a specific field type, like additional date fields that are used to trigger emails.

2.  Storing multiple records per email address
A custom table can be utilized when a customer needs to store multiple records of data relating to orders, events, tickets, and more. In this instance, one email address needs the ability to store multiple records.

3.  Sharing multiple records between more than one email address
A custom table can be utilized when a customer needs to store and share multiple records of data between multiple contacts relating to events, properties, and more. In this instance, an email address not only needs to store multiple records, but share them as well with other email addresses.

4.  Connecting relational data to Contact records
The most complex example of a custom table is when a customer needs to create a relationship between tables that do not have a direct correlation in order to connect data to a Contact email address. For instance, restaurant operating hours need to be merged into patron emails but that information is not stored on contact records.

What table relationships are supported in Delivra?

There is no one-size-fits all table structure that can match all data needs. That’s why we offer a variety of table relationships. The following relationships are available in Delivra:

1.  One-to-One
In a one-to-one relationship, one record in table A is associated with one and only one record in table B. Example: At a university, a student email account is linked to one and only one unique student ID.

2.  One-to-Many or Many-to-One
In one-to-many relationships, one record in table A is associated with multiple records in table B (and vice-versa for many-to-one). Example: At a veterinary clinic, a client account can have multiple pet records associated.

3.  Many-to-Many
A many-to-many relationship occurs when multiple records in table A are associated with multiple records in table B. Example: In terms of retail, an order may contain many products and a product may appear in many orders.

Use Case Examples for Each Relationship

Next let’s discuss use case examples for each of the table relationships outlined.

1.  One-to-one
A one-to-one relationship occurs when a single record in table A is associated with one and only one record in table B.

• The key for this relationship is email address.
• An example could be that a customer has used all numeric fields on the default member table. Adding a custom table to their account can extend contact data storage.

2.  One-to-many (also applies to many-to-one)
A one-to-many relationship occurs when a single record in table A is associated with multiple records in table B (and vice-versa).

• The key for this relationship is never an email address. Some other identifier like an account #, order #, etc. that uniquely identifies records should be used.
  • Keys are used to determine when a new row should be created versus an update to an existing row.
  • A table may be setup using more than one key.
• An example could be that one credit union member email address is linked to multiple products like checking, home equity line of credit and an auto loan.

3.  Many-to-many 
A many-to-many relationship occurs when multiple records in table A are associated with multiple records in table B.

• The key for this relationship is also never an email address. Some other identifier like account #, property ID, parcel #, etc. that uniquely identifies records should be used.
• An example could be that multiple contact email addresses are linked to multiple property IDs to receive tax information for each property they own together.
  
  

Relational Tables

Above you learned that a relationship between tables is formed when records from one table are associated with records in another table. However, a direct correlation between tables may not always exist which is the case with many-to-many relationships. When this occurs, another table is needed to bridge the data. This table is called a junction, or joining table. 

• A junction table contains the primary key columns from the tables that need to be related.
• The purpose of the junction is to build an ID structure that creates the relationship between two (or more) otherwise unrelated tables. 
• The result is a “relational” table that contains data from both of the original tables, like the customer purchase table we see on the right.
  • • In the below example, the keys from the customer table and the product table are used to create a mapping in the junction table and establish a separate customer purchase table. The “Customer Product Mapping” table is the junction and “Customer_Purchase_Table” is the resulting “relational” table that is created.
      
      
      

Notes (relational tables)

1.  Relational tables are generally used in many-to-many table relationships. This functionality offers a way for our customers to connect table data that does not have a direct correlation to contact records.

2.  Once relational tables exist in a customer account, CSV files can be uploaded to populate data in many-to-many tables. This data can be used in segmentation, automation, content merges and more.  We’re doing the work on the back-end to make the connections between tables.

3. Segment clauses are particularly important-- All segments will require a clause that connects the tables through use of a common piece of data.

4.  The junction table is not visible in a customer account. They will only see the resulting related tables. 

5.  Customers cannot set up relational tables on their own, it will require initial setup by our Engineering team.

6.  Availability of Relational tables depend on your license. Please contact our Client Success team if you are interested in Relational tables.