Database Relationships Explained

This article will help you understand the concept of the database relationships. Understating the database relationships will help you make your decisions fast while designing your database or while planning an application.

We will be covering the following relationships

1. One to One
2. One to Many - Many to One
3. Many to Many
4. Polymorphic Relationships : Morph to Many & Morph to One

For the purpose of this article, let’s suppose we will build a simple blog. And to simplify this even further, we will use the Laravel framework as the base for the naming techniques and giving more details.

Now, What are the main models of any blog?

• categories
• articles
• tags
• comments
• and let’s also add a photo gallery to this blog.

The base of our tables:

we can see that we can’t connect any of the tables with the other ones because there is no relation described in any of them.

So, Let’s start fixing our tables!

let’s start with our first table, the category table:

let’s say each category can have subcategories. This simply can be described as (one to many) relationship. But how can we achieve this? the solution is simple; we will add parent_id to our categories table.

Now, suppose that each article belongs to one author, and of course,each author has many articles. That will introduce us to a (Many to One) or a (One to Many inverse) relationship.

To achieve this we'll add (author_id) to our article table

For the sake of this article, let’s say each author has one address. So, let’s add the address table:

Now, to connect this table records to the author, we will use a relationship called (One to One)

we will add a column to our addresses table called author_id.

Things started to get complicated!

our articles sometimes may belong to more than one category. for such a problem we have a relationship type called (Many to Many) which means, each model (model 1) can contain multiple of another model (model 2) , and vice versa.

To apply this type of relationship, we need another database table, that connects those two tables together. let’s build the third table which has both singular names of the the relation tables:

Polymorphic database relationships

This is the most complicated type of the database relationships But they are very important, if we don't use them, we may duplicate tables and that will make our database bigger which has to be avoided as much as we can when designing a database.

Now for the comments, suppose we need the user to comment on both our gallery images and our articles, for this we have to use (morph to one) which can be used to connect each model row from one model to only one row from multiple models to use that we don't need to make a separate table, we can use it like this:

We will add two columns to our comments table

And finally, the tags table. We need to write tags that are connected to both our articles and our gallery photos.

Suppose we have the tag #web , when we call this tag, we want to retrieve all the articles and/or the photos that have this tag. To do so, we use (morph to many) database relation. This means each model can be connected to more than one model. To do so, we will need a new table and let's call it tagable. This table has those columns

Here, the tagable_id means the relationship model ID (ex: the article id), the tagable_type means the type of the relationship model (ex: article).

Recap

• One to one can be used to connect one record from a model to another record from another model. (Not popular unless the other table has many columns)
• One to many can be used to connect one record from a model to many records from another model
• Many to many can be used to connect many records from a model to another many records of another model, it needs a separate table to do so.
• Polymorphic relationships are used to connect one model to multiple types of models, this also needs a separate table.

References:

1. Eloquent: Relationships from Laravel Documintations link
2. SQL for Beginners: Part 3 - Database Relationships by Burak Guzel link
3. The 3 Types of Relationships in Database Design by IAN link