loading...
Cover image for Laravel Tutorial #3: Database Structure

Laravel Tutorial #3: Database Structure

ericnanhu profile image Eric Hu Originally published at techjblog.com ・5 min read

For a simple blogging system, we need at least 4 database tables: Users, Categories, Tags, and Posts. If you want other functions for your blog, comments, for example, you can add other tables yourself. To keep this tutorial short and easy to understand, these four are all we need. But, first, we need to setup our database.

Users Table

Design

Column Name Column Type Other Information
id integer auto increment
name string cannot be empty
email string unique, cannot be empty
password string

User Migration

Migration files determine the structure for the corresponding database table.

The migration file for the Users table is already included in Laravel:

database/migrations/create_users_table.php:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

User Model

The model is in charge of manipulating data stored in the database table, but in order to do that, we need to specify what columns are created.

The model for the “users” table is included in Laravel as well.

app/User.php:

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];
}

Categories Table

Design

index integer auto increment
name string cannot be empty
slug string unique, cannot be empty
description text can be empty

Categories Migration

Using Laravel’s artisan tool, we can generate the Model and the corresponding migration file together, type in the following command in the terminal:

php artisan make:model Category --migration

database/migrations/create_categories_table.php:

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->timestamps();
    });
}

Line 4, the bigIncrements('id') method creates an index column of type “bigInteger” named “id“. Line 5, timestamps() creates two columns that store the time when the record is created and when it is updated.

There are a lot more column types that you can choose from. Here is a full list of all column types that are available. List of All Available Column Types for Laravel Schema Builder

Now we can add more columns for our database.

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->string('slug')->unique();
        $table->text('description')->nullable();
        $table->timestamps();
    });
}

Line 6, unique() means each record in the column “slug” is unique.

Line 7, nullable() means the record in the column can be empty, nullable(false) means it cannot be.

These are called modifiers, they are the additional information regarding the column. This is a list of all available modifiers. List of All Available Column Modifiers for Laravel Schema Builder

Category Model

app/Category.php:

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Category extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'description',
        'slug',
    ];
}

Here, we only define the $fillable, Laravel will assume the corresponding table is “categories“, and the $primaryKey is “id“. If not, you need to specify them like this.

Tags Table

Design

id integer auto increment
name string cannot be empty
slug string unique, cannot be empty
description text can be empty

Tags Migration

php artisan make:model Tag --migration

database/migrations/create_tags_table.php:

public function up()
{
    Schema::create('tags', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->string('slug')->unique();
        $table->text('description')->nullable();
        $table->timestamps();
    });
}

Tag Model

Tag.php:

protected $fillable = [
    'name',
    'description',
    'slug',
];

Posts Table

Design

id integer auto increment
title string cannot be empty
slug string unique, cannot be empty
featured image string or text can be empty
content text cannot be empty
published boolean
featured boolean

Posts Migration

php artisan make:model Post --migration

database/migrations/create_posts_table.php:

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->bigInteger('category_id');
        $table->bigInteger('user_id');
        $table->string('title');
        $table->string('slug')->unique();
        $table->text('content');
        $table->string('featured_image')->nullable();
        $table->boolean('is_featured')->default(false);
        $table->boolean('is_published')->default(false);
        $table->timestamps();
    });
}

You may notice that there are two more “id” columns, “category_id” and “user_id“. These are used to store which category and which user that post belongs to.

They are a part of relationships and I will explain this in detail in the next section of this tutorial.

Post Model

Post.php:

protected $fillable = [
    'category_id',
    'user_id',
    "title",
    'content',
    'slug',
    'featured_image',
    'is_featured',
    'is_published'
];

Migrate

Before migrating, we need to create a new database for our project. Open the XAMPP control panel, start the Apache server and MySQL database.

img

Go to http://localhost/phpmyadmin/,

img

Create a new database:

img

Go back to our project, open .env file and edit these lines:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your-database-name
DB_USERNAME=root
DB_PASSWORD=

The default user name is root and the password is empty.

Now Laravel should be able to connect to the database, and we can start migrating.

Run migration:

php artisan migrate

Rollback a migration (if you find any mistakes):

php artisan migrate:rollback

Reset all migrations:

php artisan migrate:reset

Rollback all migrations and then migrate again:

php artisan migrate:refresh

Next Post: Laravel Tutorial #4: Relationships

Related Articles

How to Make Your Server More Secure

Laravel Tutorial For Beginners

Django Tutorial For Beginners

Discussion

pic
Editor guide