Relational Databases 101
The biggest strength of Directus is that, unlike other content management systems, it doesn’t alter your data in any way. In other words: your content is not black-boxed within a proprietary system.
Directus mirrors your database exactly, making no assumptions about how you might structure your data. Therefore, a little knowledge about database architecture is required to truly unlock its power.
In this article, we’ll take a look at the whats, whys, and hows of relational database architecture.
Important note: While certain best practices and industry standards exist, there are many different ways of solving these common problems. Directus doesn’t judge you on your database architecture.
What is this “Relational Database” thing?
In relational database software, like MySQL or PostgreSQL, data is stored in so-called ‘tables’. Just like tables in spreadsheets, each table has columns and rows. The columns are the different types of data you store (fields), and the rows are the actual data (items). Lets say we’re setting up the architecture for storing blog posts. The columns of a post might be: title, content, and featured_image.
There is one logistical issue where the relational in relational database comes into play. Let’s say we want to save who wrote a particular post. We could add extra columns to the posts table in order to save information about the author for each post. That would mean we’d end up with something like this:
| title | content | author_firstname | author_lastname |
|----------------|---------|------------------|-----------------|
| Permissions | ... | Rijk | van Zanten |
| 3rd Party auth | ... | Rijk | van Zanten |
| Thumbnailer | ... | Ben | Haynes |While this technically gets the job done, it’s far from ideal. There are two main issues with this way of storing data: data is duplicated across the table, which in turn makes it very hard to update it later. Just think about what happens if we suddenly found out there a spelling error snuck into my name. We’d have to go in and check every row in this table and change the author’s info!
In relational database architecture, it’s common to store different things (entities) in different places (tables), in order to easily retrieve and update them when needed. The two entities in play here would be posts and authors.
The main idea here is that you give every row a unique identifier which you can use to reference it in other tables. In most database softwares, having this unique identifier is required. Splitting up the two entities in different tables would look something like this:
Posts| title | content | author_id |
|----------------|---------|-----------|
| Permissions | ... | 1 |
| 3rd Party auth | ... | 1 |
| Thumbnailer | ... | 2 |
Authors| id | firstname | lastname |
|----|-----------|------------|
| 1 | Rijk | van Zanten |
| 2 | Ben | Haynes |
As you can tell from the example above, the actual data of the authors is only stored once, and being referenced by multiple other pieces of data.
Note: This form of removing duplicate data is called data normalization.
This particular relationship is called a many-to-one relationship. You can have many posts referring to one author.
On Data Types
In most databases, the type of value you can store inside each column is set in advance. These ‘data types’ specify the type of data that a column can hold. In MySQL, these data types are divided in three main types: text, number, and date.
For a list of all the different available data types (and when to use which), checkout this resource on the subject: MySQL — Data Types.
How Directus ties in with this
In Directus, the first order of business is setting up your schema based on the same principles as a relational database. This is because Directus maintains a 1-to-1 sync between your architecture and the actual database — without storing your data in some proprietary way — making Directus a very powerful database manager.
Directus offers a multitude of different interfaces to enter and edit data. Each possible data type has one or multiple interfaces. These interfaces range from sliders for numeric values to full-featured WYSIWYG editors for longer formatted text values.
Next to single-value interfaces, Directus offers some very powerful relational interfaces that make it possible to set up relationships — like the one in the example above — from within the admin interface. To learn more about these relationships, and how to set them up, check out our article: Understanding Relationships — It isn’t complicated.
Happy coding!
Your friends at Directus
🐰
PS: Have a good idea on a topic for following articles? Let us know in the comments or join us over on our public Slack channel!
