In the world of data storage, organization is essential – and one of the easiest ways to organize data is by creating relationships between data points. This is the basis of the relational database, which organizes data in predefined relationships where data is stored in tables of columns and rows (In Directus, these tables are called Collections). This structure makes it easy to understand how the various data collections relate to each other.
Like with people or inanimate objects, data can relate to other data in a variety of different ways. And the most common way to interact with a relational database system is with Structured Query Language (SQL). Directus supports all major SQL vendors and all standard database relationship types, as well as a few compound types that are unique to our platform and custom-tailored to make some common but complex tasks easier to perform.
This blog post is the first in a 5-part series about database relationships. Here we provide a brief overview of the various types of database relationships along with examples and use cases. In subsequent posts, we’ll explore each type of relationship in greater detail.
It’s All about Connections
Whereas non-relational databases are document-oriented in that all data is stored in sort of a laundry list, relational databases organize data into tables. Connections between collections of data can be established to create bi-directional relationships. This is done using a data point primary key – a unique identifier for every data point in a database.
Let’s explore the five key types of data relationships:
1. Many to One: The Basis for All Relationships
Many to One (M2O) is the basis of all relationships in relational databases. In an M2O relationship, multiple items from a Parent collection are linked to one item in a related collection. For example, say you have a Contacts collection and you want to link them to the companies to which they belong (assuming each contact only works for one company).
Let's take a look at the schema.
Relational databases only deal with items in terms of technical keys, which isn’t a great user experience. Directus enables you to display the relational data within the Parent collection and the related collection via configurable relational fields. In this example, you can simply create a Company collection, then add a Company key field to the existing Contacts collection.
You can then choose from multiple interfaces, including radio buttons like in the image below.
Why use the M2O relationship model? To avoid storing data in two places. For example, if you say these five contacts work for one company, you don’t have to keep a separate list of those five contacts, or store all the company information on each contact. These methods not only tax your resources, they could result in inconsistencies in the data. By creating an M2O relationship, you streamline data storage and help to ensure access to the most accurate data.
In Directus Data Studio, by default, when you open an Item page in Company, you won’t automatically see the related Contacts. However, Directus enables you to easily create the corresponding One to Many (O2M) field – the inverse of an M2O relationship.
2. One to Many: Many to One, Inversed
In a relational database, an O2M relationship is the exact same relationship as M2O – it’s just from the perspective of the other collection. So in the Contacts and Company example, the Company collection has an O2M relationship with Contacts.
In Directus, configuring an O2M relationship creates an Alias field – a virtual field that doesn’t map directly to an actual database column. This field simply provides access to related items with the Item Details page on items within the related collection.
You won’t always need to set this up, but it’s handy if you want to easily access related data from both sides of the relationship. For example, you may want to see what Contacts belong to a Company you’re working with.
3. One to One: Uniquely constrained
One to One (O2O) relationships are also the same thing as M2O, except that the relationship has a unique constraint. One item of the Parent collection can be linked with just one item in the related collection, and vice versa. For example, a company can have multiple contacts (O2M) but only one CEO (O2O).
4. Many to Many: Bridging the Data
In the first three types of relationships, there are only two collections, with one primary key column that links the Parent collection to related collections. Many to Many (M2M) relationships, by contrast, have two primary key columns stored within an additional table in between, often called a Junction table.
The Junction table stores each linked row between the related collections, enabling you to enabling you to relate unlimited items between two collections in either direction. For example, if you have a collection of recipes and a collection of ingredients, you can use a Junction table to create an M2M relationship between the two collections that stores the different combinations of ingredients per recipe. The Junction table will contain primary keys from both collections:
Through Directus, you can add contextual fields to track quantities or other parameters related to the various collections. For example, a quantity field in the recipe Junction table can track how much of each ingredient is needed for the recipe.
5. Many to ANY: A Unique Relationship
Directus enables Many to Any (M2A) relationships – sometimes called matrix fields or replicators – allowing you to link items from a Parent collection to any item in any database collection. M2A relationships require Alias fields as well Junction tables, but the Junction table has a field that stores the key for related collections.
M2A relationships can be used for things like page builders, where multiple collections are leveraged to build a page, for example, headings, images, videos, and so on.
What About Translations?
In addition to the five relationship types described above, Directus provides a special relational interface designed specifically to handle translations.
Essentially, a Translations O2M interface has an M2M relationship behind the scenes. When you create a Translations O2M in the Directus Data Studio, Directus automatically produces a Translations O2M Alias field, a Junction table and a languages collection. All of your translations are stored within context fields, configured by you, on the Junction table. Using the Directus Translations O2M Alias field option makes the translation process a lot quicker and easier.
Directus and Relationships: A Match Made in SQL
As with everything in Directus, you are never bound by what we offer! Directus never enforces opinionated schemas, rules or limitations, so you can build data models however you wish. However, Directus Data Studio simplifies and accelerates the process of configuring relational data models with intuitive no-code configuration. To read a more detailed description of how to implement these relationships in your Directus project, check out our technical documentation.
In the next blog post in this series, we’ll delve into Many to One relationships in greater detail. Stay tuned!
Ready to give Directus a try? Create a Directus Cloud today!