Relational objects in a data synchronization

This article describes different patterns to implement a one-way data sync of relational objects, for example, Companies and Contacts, where contacts (child objects) are linked to a Company (parent objects).

The challenge of data sync with relational is that the parent object must exist in the destination before the child can be created in the destination.

On top of that, the child object must be linked to the parent object in the destination. This link is in many cases done via a Foreign Key, meaning the id of the parent in the destination. This can be a challenge since this id is different from the id in the source.

Example:

• Source Webshop: Contact A with id 123 is linked to Company B with id 567
• Destination CRM: Contact A with id 777 must link to Company B with id 888

Example pattern with relational objects - only create child objects

In the following example, we sync Contacts from a Demo Webshop to a Demo CRM. We assume that the parent objects (companies) already exist in the destination Demo CRM. We search for the existing company by VAT number, in order to have its id that we use as Foreign Key.

When we upsert (create or update) the contact (child object), we use the id (Foreign Key) of the company from the destination:

Pattern to create child and parent objects

In the following example pattern, we upsert the parent object first, and then use the response from this upsert to have the id of the parent (used as Foreign Key when upserting the child):

Pattern with lookup in a list for parent objects

Sometimes it's impossible to quickly find the parent object in the destination, for example, because the Get company by VAT number is not available. In that case, we can retrieve a full list of parent objects from the destination, and do lookups to find the Foreign Key. This pattern will only work if the number of parent objects is limited (e.g. thousands of records but not millions of records).

Racing conditions - parent object does not exist yet

The following pattern can cause racing conditions. The parent objects are synced first, and then the children are synced. Let's assume the first part (syncing the parent objects) takes 10 minutes, and during this loop, a new parent object is created in the source. This new parent will not yet be part of the loop and will not be created in the destination. When the children are synced, the new parent may be missing in the destination, causing the creation of the child to fail.

Example pattern that may cause racing conditions:

Handling racing conditions on error

The solution is to handle the racing condition (catch the error), and create a parent object when missing:

Use the error formula to read the error from the block Add or update contact: