List relations
Common database models, like the ones used for a CRM platform, will have a structure like this:
In the example above we have multiple tables that each contain different chunks of information. If you want to know which customer bought a specific product, you would need to combine the data from 4 different tables (Retailer Users, Orders, Orderlines & Products). This way of combining data is not possible in Selligent Campaign. The system can only access data in tables that are directly linked to your master table. For journeys this will always be the audience list. This implies that the Selligent database model should always be a ‘star’, with the audience list in the center. There are exceptions but this is the general rule.
In the example above, USERS_RETAILER would be in the center and only data from DATA_ORDERS is accessible. The data needs to be crunched first in order to get information from the products table. We can make selections from the orders table, but cannot use it for personalization in journeys, because it is related 1-on-many (1:N). Selligent Campaign cannot guess which record should be selected to personalize for a contact. If you want to use order data to personalize, the data has to be aggregated in a 1-on-1 linked list to the audience list. For instance the number of orders (order records) aggregated in a ORDER_COUNT field in a 1:1 linked list "ORDER_INFO"
These are the 2 major relations:
- 1:1 (profile extensions), used for selection and personalization. It can be considered as a expansion of your audience list.
- 1:N (lookup tables), used only for selections, not personalization. It is not possible to update records using default journey components, except for a custom Stored Procedure component
The 'Relations' tab visualizes all dependencies between the selected list and the other ones.
Double clicking an existing relation opens the properties dialog for that relation. The same dialog is displayed when creating a new relation.
- Scope: the relation name which is used in the Editor and Journey section to identify the linked list. If the relation is 1:1 fields from the linked list can be used for personalization and segmentation in the editor. E.g. ~LOYALTY_CARD.POINTS~
- Description: a summary of the relation
- Master table: the current selected list. Usually the audience list
- Relation: the type of relation between parent and child. 1:1, 1:N and rarely used N:1, N:N
- Slave table: list targeted by the relation. the drop down from which a list can be selected provides information on the type of list and the table name.
- Matching keys: a mapping between the key fields in both lists used to match the record in the child list (foreign key) to the record in the master list.
When mapping of multiple fields are required, this is done from the 'Custom relation' field by entering a SQL statement manually. For instance if a contest list holds multiple contest records for a contact (normally 1:N), you can create a 1:1 relation with mapping keys ID on USERID, and a custom relation CONTEST3.CONTESTID=3, where"CONTEST3" is the scope name.
Technical note on relation types:
(writing convention parent list: child list)
1:1 relation is also called a profile extension. Example: one contact can enter one contest
1:n: one parent list entry is linked to multiple child list entries. Example: one contact can buy multiple products
n:n multiple parent list entries are linked to multiple child list entries. Example: multiple products can be bought by multiple contacts
n:1: multiple parent list entries are linked to the one child list entry. Example: multiple contacts can be linked to the same address.
