Before building your relational tables, you must determine a design, or schema, for your tables. Designing a schema requires an understanding of the basics of relational table design.
To help illustrate relational table use, a real world example use case is provided. It demonstrates an important point: there is often no one "right" way to construct a relational table setup. When approaching this feature, you must consider your needs, construct models, compare those models and pick the one that works best for you.
Before reading through this article, please read the introductory articles which describe the relational tables feature.
This article assumes that you are already familiar the relational table types.
Example Use Case
A company has an online store which allows customers to place items into a wish list. The company wants to send a series of emails which contain information about products that their customers have placed in their wish lists. They also want the messages to contain related product recommendations.
The body of the messages is divided into two sections: the top shows the items in the customer's wish list, the bottom shows the recommended products.
After studying their needs, four options are presented. Each requires the system table named "contacts", containing all emails in your Realm. It is included by default in all Realms, and does not need to be created.
At the end of each option, a list of pros and cons are presented to explain the positives and negatives for each approach.
Option One
The first approach uses three tables: contacts, wishlist, and recommended.
In addition to the default contacts table, two separate regular type relational tables must be created: wishlist and recommended.
The wishlist table stores all data for the items in the contact’s wish list. This includes basics like product name and other product specific information (e.g. how many in the wishlist, current price, and description).
The recommended table stores all data for recommended products. This table is essentially designed like the wishlist table.
Pros
- It is very efficient from a publishing perspective. Since the system is pulling all relevant data from each table, there is no slowdown due to where clauses in a data query.
- Data management is simple for the company. Before deploying a message, they must import all relevant data for each product set (wish list and recommended) into only one of two tables; there is no need to manage data in other joined tables.
- It is easy to define within the message template.
Cons
- It is not easily expandable. If the company wanted to create other tables later, a redesign may be necessary.
- It does not allow for many secondary usages (e.g. segmentation or custom report generation).
Option Two
The second approach uses two tables: contacts and wishlist.
In addition to the default contacts table, only one regular type relational table must be created: wishlist.
The wishlist table stores all data for the items in the contact’s wish list, plus the recommended products. This includes basics like product name and other product specific information (e.g. how many in the wishlist, current price, and description).
Since both wish list and recommended products are held in the wishlist table, an extra field must be used to identify if the product is from the wishlist or the recommended list.
Pros
- It is very efficient from a publishing perspective. Since the system is pulling all relevant relational data from a single table, there is no slowdown due to where clauses in a data query.
- Data management is simple for the company. Before deploying a message, they must import all relevant data for each product set (wish list and recommended) into a single table; there is no need to manage data in other joined tables.
- It is easy to define within the message template
Cons
- It is not easily expandable. If the company wanted to create other tables later, a redesign may be necessary.
- It does not allow for many secondary usages (e.g. segmentation or custom report generation).
Option Three
The third option gets into more complex design, using four tables: contacts, wishlist, recommended, and products.
In addition to the default contacts table, three relational tables must be defined:
- One flat type table to store product details.
- One join type table to store wishlist items from the product table for each contact.
- One join type table to store recommended items from the product table for each contact.
The products table stores all products and related product information (e.g. name, price, description, and a unique product identifier).
The wishlist and recommended tables are used to join the contacts and products tables. Each entry includes an identifier for a product in the products table, as well as the contact connected to that product. This allows the system to pull in the correct product information for each wish list or recommended item.
Pros
- It allows for secondary usage (e.g. segmentation or custom report generation).
- It allows for expansion (addition of tables).
Cons
- It is less efficient from a publishing perspective due to the data table joins.
- Data is not as easy to manage from the company's side. When they want to send out the message, they may need to update all three tables: wishlist, recommended and products.
- Template definition is more complex because data joins and where clauses are both required.
- It adds more possible points of failure if data is incorrect in any one of the multiple tables.
Option Four
The final option requires three tables: contacts, wishlist, and products.
In addition to the default contacts table, two relational tables must be defined:
- One flat type table to store product details.
- One join type table to store wishlist and recommended items from the product table for each contact.
The products table stores all products and related product information (e.g. name, price, description, and a unique product identifier).
The wishlist table again combines the wishlist and recommended tables seen in the previous use case. Since both wish list and recommended products are stored in the wishlist table, an extra field, wishlist_item_type, must be used to identify if the product is from the wishlist or the recommended list.
Pros
- It allows for secondary usage (e.g. segmentation or custom report generation).
- It allows expansion (addition of tables).
- Since there are fewer tables, updates are easier than in Option Three.
Cons
- It is less efficient from a publishing perspective due to the data table joins.
- Data is not as easy to manage from the company's side. When they want to send out the message, they may need to update two tables: wishlist and products.
- Template definition is more complex because data joins and where clauses are both required.
- It adds more possible points of failure if data is incorrect in any one of the multiple tables.
Note on Pros and Cons
When evaluating the design options, consider more than the number of pros versus cons. More cons than pros does not mean that it is a bad option. Though Option Three has more cons than pros, it provides the ability to create segmentation rules, which may be very important to campaign deployment later. The importance of one pro may far outweigh the cons.