Osijek,
Croatia
+385 95 578 4732
MON - FRI, 4PM - 7PM
ivan@javorovic.com
REPLY IN 48 HOURS
Udemy Course
Ivan Javorović profile image

Ivan Javorović

Full Stack Developer

  • BORN : October 3, 1995
  • NICKNAME : Jawe
  • FAVORITE BAND : The Beatles

Laravel 5.7 blog database structure (MYSQL) 2018

13,753 views

blog, Laravel, Database, Structure, mysql, cmd, vertabelo, comments, likes

DB structure which we will be creating (made on vertabelo):


With this database structure we are connecting users, tags and posts together (directly and with relation tables).

The users table in the example doesn't contain any personal data because it's not included in the project below. It's only used as a reference (so you can perceive it better).


HOW IT WOULD BE USED IN PRACTICE:


After a user is registered they are added to the users table. By logging in they have an option to add a post (fill out a post form).

The form input consists of: title, slug, description, short description, published, tags, img.


- title (100 chars) - name of the post you are publishing

- slug (500 chars) - the link of the post (eg. my-laravel-project)

- description (mediumText) - the content of the actual post, text, images, videos etc.

- short description (160 chars) - SEO description of the post, this shows up on google rankings

- tags (array) - an array of tags which will later be connected to the actual post

- published (boolean) - checkbox for Draft/Published content, determines if your content is shown on your site

- img (500 chars) - name of the image that is saved in a certain folder on your site so you can display it for your blog post


Also by looking at the database structure above you can see that not all data is saved within one table. The first table that is populated with data is posts.


By making a row in that table we are able to generate a post_id with which we can populate the other tables such as post_descriptions, post_short_descriptions, post_tags, post_views etc.


The post_views table for example is automatically generated when the post is made, with the column views being set to 0. Meaning the actual post has 0 views when it's made.


The views are generated when a user clicks on a post, but to stop users from refreshing a single page and incrementing a huge number of views we made a user_views table which holds a user_id and post_id so we can check if a certain user has already viewed a post. If they didn't view it the views column is incremented by 1.


Also worth noting is that when you generate tags for articles you should check the database (in this case the tags table) if a certain tag already exists, to stop duplicates.


For example you're writing a article about cars, and you're using a tag called 'cars' (which you already used in a previous article), you should make it so you check if a tag with the name 'cars' exists in the tags table, and instead of creating a new row, you should fetch the id of that row and insert it into the post_tags table directly.


LARAVEL MIGRATION AND MODEL GUIDE:


Let's create a fresh Laravel project! (it can be used on an existing one as well)

Run the following command in cmd:

laravel new blogdb


Now let's edit the .env file to connect to our DB:

DB_DATABASE=blogdb
DB_USERNAME=root
DB_PASSWORD=


Also edit the config/database.php file (mysql settings):

'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => 'InnoDB',
],


The reason we set the engine to 'InnoDB' is because we are going to be using foreign keys in our project.


After editing our project settings we need to generate the necessary models and migrations!

Models and migrations:

php artisan make:model Tag -m
php artisan make:model Post -m
php artisan make:model PostDescription -m
php artisan make:model PostShortDescription -m
php artisan make:model PostTag -m
php artisan make:model PostView -m
php artisan make:model UserView -m
php artisan make:model PostComment -m
php artisan make:model PostLike -m


Edit your migrations like below:

posts_table

Schema::create('posts', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->string('slug')->unique();
$table->string('title', 100);
$table->string('img');
$table->boolean('published')->default(0);
$table->timestamps();
});


post_descriptions_table

Schema::create('post_descriptions', function (Blueprint $table) {
$table->increments('id');
$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
$table->longText('description');
$table->timestamps();
});


post_short_descriptions_table

Schema::create('post_short_descriptions', function (Blueprint $table) {
$table->increments('id');
$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
$table->string('short_description', 160);
$table->timestamps();
});


post_views_table

Schema::create('post_views', function (Blueprint $table) {
$table->increments('id');
$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
$table->integer('views')->unsigned()->default(0);
$table->timestamps();
});


tags_table

Schema::create('tags', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->timestamps();
});


post_tags_table

Schema::create('post_tags', function (Blueprint $table) {
$table->increments('id');
$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
$table->integer('tag_id')->unsigned();
$table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
$table->timestamps();
});


user_views_table

Schema::create('user_views', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
$table->timestamps();
});


post_likes_table

Schema::create('post_likes', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
$table->boolean('is_liked');
$table->timestamps();
});


post_comments_table

Schema::create('post_comments', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
$table->string('comment', 1000);
$table->timestamps();
});


If you want a good book for understanding the mySQL database structure and functionality then I would highly recommend one of these books:

>>> MySQL Crash Course


 >>> Jump Start MySQL: Master the Database That Powers the Web

By: Ivan Javorović

Your experience on this site will be improved by allowing cookies.