Overview
Outlines the relationships between emails, email addresses, and bean records.
Email Tables
Table Name | Description |
---|---|
email_addresses | Each record in this table represents an email address in the system. Note that the invalid_email column and opt_out column are stored on this table, meaning that they are treated as properties of the email address itself, not a relationship attribute between a given email address and related record. This means if a Lead opts-out of a campaign, this email address will be considered opted-out in all contexts, not just with further correspondence with that specific Lead. |
email_addr_bean_rel | The email_addr_bean_rel table maintains the relationship between the email address and its parent module record (Contacts, Accounts, Leads, Users, etc) to determine which record of the given module the email address belongs to. Note that this relationship table also has the primary_address and reply_to_address columns to indicate whether a given email address for a given record is the primary email address, the reply to email address, or some other address for the contact. A contact can have one address flagged as primary, and one flagged as "Reply To". This can be the same email address or two different email addresses. |
emails_email_addr_rel | The emails_email_addr_rel table maintains the relationships between the email address and email records. Note that this relationship table also has the address_type column to indicate if the email address is related to the email as a "To", "From", "CC", or "BCC" address. The valid values at the database level for this column are: from, to, cc, bcc. |
emails | Each record in this table represents an email in the system. Note that emails fall outside the scope of this document, but are mentioned here for clarity, as the two modules are closely related. |
emails_beans | Similar to the email_addr_bean_rel table, the emails_beans table maintains the relationship between an email record and any related records (Contacts, Accounts, Cases, etc.). |
<module> | This is used to show how an email address record relates to a record in any module is set on bean_module . If bean_module is set to Contacts , <module> would be the contacts table. |
The following diagram illustrates table relationships between email addresses and other modules, email addresses and email records, and email records and other modules.
Helper Queries
Retrieve the Primary Email Address of a Contact
The following query will fetch the email address given a specific contacts id:
SELECT
email_address
FROM email_addresses
JOIN email_addr_bean_rel eabr
ON eabr.email_address_id = email_addresses.id
WHERE eabr.bean_module = "Contacts"
AND eabr.bean_id = "<contact id>"
AND email_addresses.invalid_email = 0
AND eabr.deleted = 0
AND eabr.primary_address = 1;
Retrieve All Records Related to an Email Address
The following query will fetch the id and module name of all records related to the specified email address:
SELECT
bean_module,
bean_id
FROM email_addr_bean_rel eabr
JOIN email_addresses
ON eabr.email_address_id = email_addresses.id
WHERE email_addresses.email_address = "<email address>"
AND eabr.deleted = 0;
Retrieve All Emails Sent From An Email Address
The following query will fetch all emails sent from a specified email address:
SELECT
emails.name,
emails.date_sent
FROM emails
JOIN emails_email_addr_rel eear
ON eear.email_id = emails.id
JOIN email_addresses
ON eear.email_address_id = email_addresses.id
WHERE email_addresses.email_address = "<email address>"
AND eear.address_type = "from"
AND eear.deleted = 0
Cleanup Duplicate Email Addresses
The following queries will remove any duplicate email addresses.
First, create a temporary table with distinct records from the email_addr_bean_rel
table:
create table email_addr_bean_rel_tmp_ful
SELECT
*
FROM email_addr_bean_rel
WHERE deleted = '0'
GROUP BY email_address_id,
bean_module,
bean_id
ORDER BY primary_address DESC;
Next, clear out the email_addr_bean_rel
table:
truncate email_addr_bean_rel;
Move the records from the temporary table back to email_addr_bean_rel
:
INSERT INTO email_addr_bean_rel
SELECT
*
FROM email_addr_bean_rel_tmp;
Validate that all of the duplicates have been removed:
SELECT
COUNT(*) AS repetitions,
date_modified,
bean_id,
bean_module
FROM email_addr_bean_rel
WHERE deleted = '0'
GROUP BY bean_id,
bean_module,
email_address_id
HAVING repetitions > 1;
Finally, remove the temporary table:
drop table email_addr_bean_rel_tmp;
Email Address Validation
Sugar validates emails addresses according to the RFC 5321 and RFC 5322 standards. The following sections will detail how a developer can validate email addresses both server and client side.
Server Side
To validate an email address in a server-side context, the EmailAddress::isValidEmail()
static method should be used. For example:
$emailAddress = "test@example.com";
$isValid = EmailAddress::isValidEmail($emailAddress);
The EmailAddress::isValidEmail
method leverages the PHPMailer library bundled with Sugar, specifically the PHPMailer::validateAddress
method, which validates the address according to the RFC 5321 and RFC 5322 standards.
Client Side
To validate an email address client-side context, the app.utils.isValidEmailAddress()
function can be used.
var emailAddress = "test@example.com";
var isValid = app.utils.isValidEmailAddress(emailAddress);
Note: This function is more permissive and does not conform exactly to the RFC standards used on the server. As such, the email address will be validated again on the server when the record is saved, which could still fail validation.