Are one-to-many relationships in my contact model reliable?

Question

I briefly describe the contact model I have built in the Contact Builder. I have taken the Marketing Cloud Consultant course in which the lecturer stated a few exceptions where things do not work as they should when it comes to Contact Builder's relationship. So, before we start building the journey I would like to make sure things are correctly wired in my Attribute Group. There are 3 tables in this group: Customers, Bookings and Flights.

The Customers's PassengerID (unique) is connected to the ContactKey and each customer has an email address associated.

Customers has a one-to-many relationship with Flights. Each customer can fly more than one time (Customers.ID = Flights.PassengerId).

Bookings have a one-to-many relationship with Flights, too. Each booking can include more than one flight. (Bookings.BookingKey = Flights.BookingKey)

As seen above, it looks as if Flight has a central role in the model. It looks good in a database system. But I have my doubts about when we start building our journeys.

In Journey Builder, fetching the data starts at the ContactKey. E.g. if we are looking to see if a specific flight is canceled, we need to carry BookingKey containing that flight in the Entry Point Data Extension of the journey, start from ContactKey, look for the flights related to that BookingKey and check if any of them are canceled.

Does this model look stable for you? Do I need to add a middle table for my one-to-many relationships? Is Journey Builder capable of understanding this model the way SQL Server is?

Inputs and suggestions are highly appreciated.

Answer 1

Unfortunately, that's not the way Contact Data is used in Journey Builder. That is, you can't check the Flights DE (Attribute Set) to evaluate multiple records for a single Contact.

Let me describe the behaviour of one-to-many relationship evaluation. Let's say you want to include a Decision Split Activity in your journey to check for flights related to the the BookingKey, then this would only work if you have a single record for the Contact in your flights DE. If you have more than one, Journey Builder will just route Contacts down the first branch in your Decision Split Activity each time.

This probably begs the question, why can you create many-based relationships in Contact Builder? The answer is that many-based relationships are core to the Audience Builder data model. They really don't have any application outside of an Audience Builder context.

I'm assuming that you want to include a Decision Split Activity in your Journey (this wasn't explicit in your question). Assuming you do, you have a couple of options here:

Option 1: Query Activity

Create a query Activity that returns a single record for each Contact with all flights (assuming there are a pre-determined number flights). You would update a target DE at a regular interval (in an Automation) and the target DE would be used with a one-to-one relationship in your contact model. For example, a given Contact record could have field names for each flight and values with the flight status in a DE:

• FIELD NAME : FIELD VALUE
• QF1 : cancelled
• QF2 : on-time
• QF6 : delayed
• QF10 : on-time

Note that this approach isn't suitable if there are more than 99 flights, which as the recommended maximum number of fields in a DE is 100 (if you include more, then you will start experiencing performance issues due to the row size limit in MS SQL).

Option 2: Custom Split Activity

The second option would be to build your own Custom Split Activity which could use the Marketing Cloud API to retrieve Data from the Flights DE (or an external system) and return an outcome for a Contact that reaches the activity. This is a lot of effort, but if there are hundreds of flights and they change frequently, then this will give you the most flexibility and also enable you to perform this test in real-time (for example, use a flight status API to get a flight status for a given flight when a Contact reaches the custom split activity).