A relational table is a table of columns or fields that describe a listing (or rows) of data, similar to an Acoustic Campaign database. For example, a relational table may contain fields such as customer ID, transaction number, product purchased, product price, sale date, and purchase location.
There are two ways to create a relational table in Acoustic Campaign:
- By importing a CSV, TSV, or PSV file; or
- Through the API. Details are available in our developer experience: https://developer.goacoustic.com/acoustic-campaign/reference/relational-table-management
Note: The default limit on relational tables per organization is 10. If your organization needs more relational tables, contact Support.
This feature allows you to expand the data available on your contact records exponentially by mapping your database to relational tables. You can store multiple lines of data on purchases, event attendance, activities and map it to a single record, offering a holistic customer view.
When you use this feature, your data can be utilized in queries and segmentation, dynamic content and in personalization in emails.
It is beneficial to associate a database to a relational table so you can use the data from the database(s) in your emails.
Relational table one-to-many relationship
In relational databases, a one-to-many relationship occurs when a parent record in one table can potentially reference several child records in another table.
In a one-to-many relationship, the parent is not required to have child records; therefore, the one-to-many relationship allows zero child records, a single child record or multiple child records. The important thing is that the child cannot have more than one parent record.
Each table contains a Unique Identifier, a field with a distinct value for each row. The same Customer ID appears or "repeats" in multiple rows. In a proper data model, this field would be contained-uniquely-in a database, allowing you to store multiple purchases for a single contact.
The opposite of a one-to-many relationship is a many-to-many relationship, in which a child record can link back to several parent records.
Relational table many-to-one relationship
A many-to-one relationship is where one entity (typically a column or set of columns) contains values that refer to another entity (a column or set of columns) that has unique values.
In relational databases, these many-to-one relationships are often enforced by foreign key/primary key relationships, and the relationships typically are between fact and dimension tables and between levels in a hierarchy. The relationship is often used to describe classifications or groupings. For example, in a geography schema having tables Region, State, and City, there are many states that are in a given region, but no states are in two regions. Similarly for cities, a city is in only one state (cities that have the same name but are in more than one state must be handled slightly differently). The key point is that each city exists in exactly one state, but a state may have many cities, hence the term "many-to-one."
The different elements, or levels, of a hierarchy must have many-to-one relationships between children and parent levels, regardless of whether the hierarchy is physically represented in a star or snowflake schema; that is, the data must abide by these relationships. The clean data required to enforce the many-to-one relationships is an important characteristic of a dimensional schema.
If the unique field in a relational table is not repeated elsewhere in the table, you could reuse its data many times in a database. This data can be related to many contacts in the database and is an example of a many-to-one relationship.