Messages Schema

Overview

Campaign Intelligence for messages, such as emails and SMS, leverages three types of models:

1. Users are the parent model.
2. Events are events that connect users to an asset, such as email clicks or opens.
3. Emails are a type of asset.

Schema

In the entity relationship diagram (ERD) below, we show a schema for messages using emails as an example. The ERD shows expected model columns and their relationships, with necessary primary (PK) and foreign keys (FK).

In the ERD above, Users are connected to different Events -- such as email opens and clicks -- through a unique user_id. Events require a timestamp. They can have additional columns based on the Event type, such as a clicked_url for email click events.

Each Event is also linked to an Asset model, either email or SMS, by way of a message_id. The message can have a variety of columns, including date columns such as start_at and ended_at to indicate the dates that email asset was in use.