In my experiences, I saw most people struggling with database design and normalization, In the last 4/5 or years I guided many projects of my colleagues, friends, and younger brothers but they did not care about database designing and structuring.
They know about the CRUD operation in Laravel and they use it everywhere without knowing the database design and normalization.
Such as a CMS-based application that has many pages where the base columns are the same but they create a new table for each page, and they use the same CRUD operation for each table.
an example for the about page they create a table named about and for the contact page they create a table named contact and so on.
But they don't know that they can use a single table for all the pages and they can use the same CRUD operation for all the pages.
Basics of Database
A basic knowledge of databases is very important to understanding database design and normalization.
What is a Database
A database is a collection of information that is organized so that it can be easily accessed, managed, and updated.
There are many types of databases, but the most common is the relational database or RDBMS. A relational database stores data in tables, which are organized into columns and rows. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points.
For example, a company database may include tables for products, employees, and financial records. Each employee would be assigned a unique employee ID, which would be associated with information such as salary, job title, and contact information. The products table might include the product ID, product name, and quantity on hand. The financial tables would include information such as sales, expenses, and profits.
What is Database Design
Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data into the database model.
The database design process starts with the requirement analysis and conceptual design phases. These phases are usually performed by a group of people with complementary skills (e.g., data analysts, systems analysts, and database designers). The conceptual design is typically performed by data analysts and systems analysts. The database designer is responsible for converting the conceptual design into a logical design, and then into a physical design.
What is Database Normalization
Database normalization is the process of structuring a database, usually a relational database, by a series of so-called normal forms to reduce data redundancy and improve data integrity.
There are several normal forms, from the first normal form (1NF) to the fifth normal form (5NF). Each normal form builds on the previous one.
The goal of normalization is to reduce and even eliminate data redundancy, an important consideration for application developers because it is incredibly difficult to store objects in a relational database that maintains the same information in several places.
What is a Database Model
A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized and manipulated. The most popular example of a database model is the relational model, which uses a table-based format.
There are several different types of database models, including:
Hierarchical database model
Relational model
Network model
Object-oriented database model
Entity-relationship model
Document model
Entity-attribute-value model
Star schema
The object-relational model, which combines the two that make up its name
Database in Laravel
In Laravel, we can use any database we want, but I prefer MySQL. In this article, I will use MySQL. If you want to use any other database, you can use it.
Laravel provides a very simple way to create database tables. We need to create a migration file which will create a table in the database. Migration files are like version control for your database, allowing your team to easily modify and share the application's database schema. Laravel's database query builder provides a convenient, fluent interface for creating and manipulating database tables. It can be used to perform most database operations in your application and works on all supported database systems.
It is important to note that the Laravel Schema component is not a database abstraction layer. It doesn't replace the need for writing SQL statements, but it does provide a more expressive way of creating tables and columns. It also allows you to easily update your database schema from one version of your application to the next.
Database Design
In database design, at first, we have to determine which kind of application we are going to build. Then we have to determine the entities of the application. Then we have to determine the attributes of the entities. Then we have to determine the relationships between the entities.
I will use a simple blog application as an example.
So as a blog application, we will have the following entities.
Users
Posts
Categories
Tags
Comments
In those entities, Users will contain the information of the users or author. Posts will contain the information of the posts, created by the user. Categories will contain the information of the categories. Tags will contain the information of the tags. Comments will contain the information of the comments of the posts.
Now we have to determine the attributes of the entities. We will have the following attributes in the entities.
Users
id
name
email
password
remember_token
created_at
updated_at
Posts
id
user_id
category_id
title
slug
cover_image
body
created_at
updated_at
Categories
id
name
slug
created_at
updated_at
Tags
id
name
slug
created_at
updated_at
Comments
id
user_id
post_id
body
created_at
updated_at
Now we have to determine the relationships between the entities. We will have the following relationships between the entities.
Users
A user can have many posts.
A user can have many comments.
Posts
A post belongs to a user.
A post belongs to a category.
A post can have many comments.
A post can have many tags.
Categories
- A category can have many posts.
Tags
- A tag can have many posts.
Comments
A comment belongs to a user.
A comment belongs to a post.
So this is the database design of our blog application. Now we will create the database structure.
NB: Remember that, every table must have a primary key. In this article, I will use the id column as the primary key of the tables.
In database design and normalization, there are some basic rules and principles and it depends on the application you creating
Database Structure
Now we have designed our database. Let's create the database structure. We will use the Laravel migration tool to create the database structure.
Creating the Models
At first, we will create the models and migration. We will create the models using the following command.
php artisan make:model User -m
php artisan make:model Post -m
php artisan make:model Category -m
php artisan make:model Tag -m
php artisan make:model Comment -m
It will create the models and migrations in the app/Models and database/migrations directory.
Migrations schema and its explanation
So our migration files in the database/migrations directory, let’s update those.
database/migrations/create_users_table.php
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
In this schema, we have used the unique() method to make the email column unique. We have used the nullable() method to make the email_verified_at column nullable. We have used the rememberToken() method to create the remember_token column. We have used the timestamps() method to create the created_at and updated_at columns.
Here you see, we have used respective column types, which is important for database design and normalization. The name, email and password column type is a string. The email_verified_at column type is timestamp. The remember_token column type is a string. The created_at and updated_at column type is timestamp.
Every time we create a migration, we have to create the respective column type, not just the string type or text type, also you need to define your column limit as necessary.
database/migrations/create_posts_table.php
Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained()->onDelete('cascade');
$table->foreignId('category_id')->constrained()->onDelete('cascade');
$table->string('title');
$table->string('slug')->unique();
$table->string('cover_image', 255)->nullable();
$table->text('body');
$table->timestamps();
});
In this table schema, we used two foreign keys user_id and category_id, and related to the users and categories table. In this schema, we have used the constrained() method to create the foreign key. We have used the onDelete('cascade') method to delete the posts when the user or category is deleted.
Most of the time people use the unsignedBigInteger() method to create the foreign key, which works fine for the most part, but once we have some users and posts in our system - deleting a user will cause an issue (Attempt to read property "users_table_column_name" on null) because the foreign key constraint will fail. So we have to use the constrained() method to create the foreign key.
database/migrations/create_categories_table.php
Schema::create('categories', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('slug')->unique();
$table->timestamps();
});
This is as simple as you see.
database/migrations/create_tags_table.php
Schema::create('tags', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('slug')->unique();
$table->timestamps();
});
This is also simple as you see.
database/migrations/create_comments_table.php
Schema::create('comments', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained()->onDelete('cascade');
$table->foreignId('post_id')->constrained()->onDelete('cascade');
$table->text('body');
$table->timestamps();
});
In this table schema, we used two foreign key user_id and post_id, and related to the users and posts table.
The reason we use foreignId instead of unsignedBigInteger is because it's a shortcut for $table->unsignedBigInteger('user_id'); & $table->foreign('user_id')->references('id')->on('users'); and it's more readable. Also, we have used the onDelete('cascade') method to delete the comments when the user or post is deleted.
Creating the foreign key indexes
Now we have to create the foreign key indexes. We will create the foreign key indexes using the following code.
database/migrations/create_posts_table.php
Schema::table('posts', function (Blueprint $table) {
$table->index('user_id');
$table->index('category_id');
});
database/migrations/create_comments_table.php
Schema::table('comments', function (Blueprint $table) {
$table->index('user_id');
$table->index('post_id');
});
Creating the pivot table
Now we have to create the pivot table. We will create the pivot table using the following code.
database/migrations/create_post_tag_table.php
Schema::create('post_tag', function (Blueprint $table) {
$table->id();
$table->foreignId('post_id')->constrained()->onDelete('cascade');
$table->foreignId('tag_id')->constrained()->onDelete('cascade');
$table->timestamps();
});
In this table schema, we used two foreign key post_id and tag_id, and related to the posts and tags table.
Creating the relationships
Now we have to create the relationships in the models. We will create the relationships in the models using the following code.
app/Models/User.php
public function posts()
{
return $this->hasMany(Post::class);
}
public function comments()
{
return $this->hasMany(Comment::class);
}
app/Models/Post.php
public function user()
{
return $this->belongsTo(User::class);
}
public function category()
{
return $this->belongsTo(Category::class);
}
public function comments()
{
return $this->hasMany(Comment::class);
}
public function tags()
{
return $this->belongsToMany(Tag::class, 'post_tag');
}
app/Models/Category.php
public function posts()
{
return $this->hasMany(Post::class);
}
app/Models/Tag.php
public function posts()
{
return $this->belongsToMany(Post::class, 'post_tag');
}
app/Models/Comment.php
public function user()
{
return $this->belongsTo(User::class);
}
public function post()
{
return $this->belongsTo(Post::class);
}
Okay, we have created the relationships in the models. Now we have to create the database structure. We will create the database tables using the following command.
php artisan migrate
It will create the database tables.
Normalization explain
In our database, we separate the Tags without the normalization we can do it like this below.
In our database, we separate the Tags without the normalization we can do it like this below.
Schema::create('posts', function (Blueprint $table) {
$table->json('tags')->nullable();
});
But we can't use the where clause to search the tags or show the list of all tags, it is possible with this way but it will take extra time to render. So we normalize the tags and create a new table for tags.
Most of the case users are not familiar with the database design and normalization. They just create the database tables and use them. But it's not the right way. We have to design the database first, then we have to create the database structure, and we follow the best practices for the naming convention, column type, foreign key, indexes, relationships etc. To learn more about database design and normalization, you can read the following articles.
If you saw, I just put the codes in this article not the full file, you saw I never mentioned any doc-comment in all the methods I wrote, it's not a requirement that we should add doc-comments, but it will be better to add some doc-comments it will help to understand the codes to the next developer and also some editor provide definition of the method from the doc-comments.
Conclusion
In this article, we have learned about database design and normalization. We have learned how to design the database and how to create the database structure. We have learned about the naming convention, column type, foreign key, indexes, relationships etc. We have learned about the best practices for database design and normalization.
I hope you like this article. If you have any questions or suggestions, please feel free to ask me in the comment section below.