Database Relationship

4 min readNov 27, 2020

What is Database Relationship?

The backbone of any relational database is a database relationship. Once you have defined tables, you can relate the data held in different tables. In other words, database relationships are associations between tables that are created using join statements to fetch data. To achieve this one table uses a foreign key that references the primary key of another table.

Types of relationship

One-to-One

A record in Table-A relates to one matching record in Table-B, and each record in Table-B relates to one matching record in Table-A.

Each record in the Employee table is about one employee. That record relates to only one record in the Pay table. Each record in the Pay table relates only one record in the Employee table.

One-to-Many

A record in Table-A can have many matching records in Table-B, but a record in Table-B can have only one matching record in Table-A.

A one-to-many relationship is the most common relationship found between tables in a relational database. The Customer table holds a unique record for each customer. Each customer can place many orders. Many records in the Order table can relate to only one record in the Customers table.

Many-to-Many

In a many-to-many relationship, each record in both tables can relate to many records in the other table. These relationships require a third table, called an intermediary table.

The relationship between the Customer and Order table is one-to-many. But the relationship between the Order table and Product table is many-to-many. An order can contain multiple products, and a product could be linked to multiple orders as several customers might submit an order that contains the same products. This kind of relationship requires a minimum of three tables.

Now let's continue with our project from the previous part — Migration and data seeding to creating Relationship among Models. An Eloquent relationship is a very important feature in Laravel that allows you to relate the tables in a very easy manner.

Below is an ER-Diagram (An ER diagram shows the relationship among entity sets) of entities involved in our laravel-news project.

Based on the above E-R Diagram now let's start writing relationship methods in the model classes.

In our application, a category will have many posts.

class Category extends Model
{
 ...
 public function posts(){
  return $this->hasMany(Post::class);
 }
 ...
}

The comment will be written by a user and it will be on a post.

class Comment extends Model
{
  ...
  public function author(){
   return $this->belongsTo(User::class, 'author_id', 'id');  } public function post(){
   return $this->belongsTo(Post::class);
  }
  ...
}

A post will be written by an author, belongs to a category; might have many comments, images, videos, and tags.

class Post extends Model
 {
  ...
  public function author(){
   return $this->belongsTo(User::class, 'author_id', 'id');
  }   public function category(){
   return $this->belongsTo(Category::class);
  }   public function comments(){
   return $this->hasMany(Comment::class);
  }   public function images(){
   return $this->hasMany(Image::class);
  }   public function videos(){
   return $this->hasMany(Video::class);
  }   public function tags(){
   return $this->belongsToMany(Tag::class);
  }
  ...
 }

Multiple posts might belong to a tag.

class Tag extends Model
{
 ...
 public function posts(){
  return $this->belongsToMany(Post::class);
 }
 ...
}

A user may write multiple posts or comments.

class User extends Model
{
  ...
  public function comments(){
   return $this->hasMany(Comment::class, 'author_id', 'id');
  }   public function posts(){
   return $this->hasMany(Post::class, 'author_id', 'id');
  }
  ...
}

Here we are done with the relationship between models. Now let's test if the relationships are correct or not.

To do so let's create a ‘test’ route in routes\web.php

Route::get('test', function () {
$category = App\Models\Category::find(3);
// return $category->posts;$comment = App\Models\Comment::find(152);
// return $comment->author;
// return $comment->post;$post = App\Models\Post::find(152);
// return $post->category;
// return $post->author;
// return $post->images;
// return $post->comments;
// return $post->tags;$tag = App\Models\Tag::find(5);
// return $tag->posts;$author = App\Models\User::find(88);
// return $author->posts;
return $author->comments;
});