Table relationships

Table relationships allow you to keep related information organized, avoiding the creation of a single very complex and redundant table.

Let’s assume we want to link the orders table to the products purchased by the contact, and that we want to create multiple data tables. The data diagram will look similar to the one in the image:

Thanks to this data model, it is possible to create audiences containing all the contacts who purchased a specific clothing item, such as jeans.

Procedure

First of all, you need to create the tables that will store the required information, namely the products table and the orders table.

1. Create the products table with the desired columns - in this case ID, name, and price - and import the data to populate your catalog. If you don’t know how to do this, follow the dedicated guide.
   
2. Enable the contact extension for the products table. If you don’t know how to do this, follow the dedicated guide.
3. Create the orders table with the desired columns - in this case ID, total amount, purchase date, and buyer’s email - and import the data to populate your order history.
   For now, do not consider the column containing the list of products.
   
4. Enable the contact extension for the orders table.
5. Since each order can include multiple products and each product can appear in multiple orders, go to the orders data table and click the button Create a many to many relationship to link the two tables you just created and populated. In the window that appears, configure the following options:
   • Table to be connected
     specify which table will be linked to the one you are currently working on. In this case, we want to link products to orders, so select the products table.

   • Association table 
     this field is already pre-filled with the value Create a table automatically. Once the association between the two tables is completed, a third table will be created containing the IDs from the first two.
     For example, if my order with ID 512 included the products with IDs 1, 3, and 5, in this third table the order would be represented by three rows:

     

   • Field name 
     in this field, you can change the default name suggested by the platform.
   • Field associated with the Primary Key of the current table 
     indicates the field through which the correlation with the other table will occur.

   • Field connected to the key of the linked table
     indicates the field of the related table to be associated with the one you are working on 

     

6. Once the configuration is complete, click Save
   At this point, in the list of data tables, we will have three different items:
   • orders table (created manually)
   • products table (created manually)
   • orders_products association table (automatically created by the system)

7. Populate the association table, for example with a .csv file containing in each row the ID from the first table and the ID from the second, even if repeated.

   

Now that the tables are linked, to view the data directly in the contact profile, create a new association field in the database. If you don’t know how to do this, follow the dedicated guide.

If you want to display the list of associated items in the contact profile tab, enable the option Show in lists in the association field settings of the second table.
In our example, this is useful for seeing the purchased products directly in the "Orders" tab of the contact profile.

Now that the association is complete, it is possible to create audiences or filter the database based on the products purchased by users. For example, we can filter contacts to show only those who have purchased a specific product, such as jeans.