Share

In this part of this series we will talk about database, and the main tables for our CRM system, then we will create the migration files for those tables.

Series Topics:

• Part 1: Preparation
• Part 2: Database
• Part 3: Models & Relations
  
• Part 4: Preparing Login Page
  
• Part 5: Users Module
  
• Part 6: Roles & Permissions
  
• Part 7: Documents Module
  
• Part 8: Contacts Module
  
• Part 9: Tasks Module
  
• Part 10: Mailbox Module
  
• Part 11: Mailbox Module Complete
  
• Part 12: Calendar Module
  
• Part 13: Finishing

We will start by identifying the main tables we will work with then we show a visual diagram of each table and it’s fields, then we will create the migrations and at the end of this part we will add the seed data like the permissions and data for static tables.

If you remember from the previous part when we mentioned the main components of the CRM, we can summarize the main tables of the CRM:

• Contacts table: hold contact and customer data
• Tasks table: hold tasks requested from customers
• Documents table
• Mailbox table: we will cover this in other part when we talk about mailbox
• User table: hold sales and sales managers data
• Roles and permissions tables: those tables generated from the spatie/laravel-permissions package.
• Settings table: lookup table that hold the system settings

Visual diagram of some database tables and their fields:

contacts table

contact phones and contact emails

contact document and contact status

task and task related tables

users table

document tables

setting table

The above diagram is a descriptive diagram of the database tables to be able to take all overview of the database before creating the migrations.

Note that i didn’t add the roles tables because those will be generated automatically using the spatie/laravel-permissions package mentioned above.

Let’s move to creating the migrations

Generating Migrations

Run these artisan commands to generate the migration files:

php artisan make:migration create_setting_table
php artisan make:migration create_document_type_table
php artisan make:migration create_document_table
php artisan make:migration create_task_status_table
php artisan make:migration create_task_type_table
php artisan make:migration create_contact_status_table

php artisan make:migration create_contact_table
php artisan make:migration create_contact_phone_table
php artisan make:migration create_contact_email_table
php artisan make:migration create_contact_document_table
php artisan make:migration create_task_table
php artisan make:migration create_task_document_table

Next change your database settings in .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=<add database name>
DB_USERNAME=<add username>
DB_PASSWORD=<add password>

Now open your migration files and modify them as shown below:

database/migrations/XXXX_XX_XX_XXXXX_create_users_table.php

<?php

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

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->string('position_title')->nullable();
            $table->string('phone')->nullable();
            $table->string('image')->nullable();
            $table->tinyInteger('is_admin')->default(0);
            $table->tinyInteger('is_active')->default(1);
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_setting_table.php

<?php

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

class CreateSettingTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('setting', function (Blueprint $table) {
            $table->increments('id');
            $table->string('setting_key')->unique();
            $table->text('setting_value')->nullable();
            $table->timestamps();
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_document_type_table.php

<?php

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

class CreateDocumentTypeTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('document_type', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_document_table.php

<?php

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

class CreateDocumentTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('document', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('file');
            $table->tinyInteger('status')->default(1)->comment('1=active 2=not active');
            $table->integer('type')->unsigned()->nullable();
            $table->string('publish_date')->nullable();
            $table->string('expiration_date')->nullable();
            $table->integer('created_by_id')->unsigned();
            $table->integer('modified_by_id')->unsigned()->nullable();
            $table->integer('assigned_user_id')->unsigned()->nullable();

            $table->timestamps();
            $table->softDeletes();

            $table->foreign('type')->references('id')->on('document_type')->onDelete('set null');
            $table->foreign('created_by_id')->references('id')->on('users');
            $table->foreign('modified_by_id')->references('id')->on('users')->onDelete('set null');
            $table->foreign('assigned_user_id')->references('id')->on('users');
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_task_status_table.php

<?php

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

class CreateTaskStatusTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('task_status', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_task_type_table.php

<?php

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

class CreateTaskTypeTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('task_type', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_contact_status_table.php

<?php

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

class CreateContactStatusTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('contact_status', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_contact_table.php

<?php

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

class CreateContactTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('contact', function (Blueprint $table) {
            $table->increments('id');
            $table->string('first_name');
            $table->string('middle_name')->nullable();
            $table->string('last_name');
            $table->integer('status');
            $table->string('referral_source')->nullable();
            $table->string('position_title')->nullable();
            $table->string('industry')->nullable();
            $table->string('project_type')->nullable();
            $table->string('company')->nullable();
            $table->text('project_description')->nullable();
            $table->text('description')->nullable();
            $table->string('budget')->nullable();
            $table->string('website')->nullable();
            $table->string('linkedin')->nullable();
            $table->string('address_street')->nullable();
            $table->string('address_city')->nullable();
            $table->string('address_state')->nullable();
            $table->string('address_country')->nullable();
            $table->string('address_zipcode')->nullable();
            $table->integer('created_by_id')->unsigned();
            $table->integer('modified_by_id')->unsigned()->nullable();
            $table->integer('assigned_user_id')->unsigned()->nullable();

            $table->timestamps();
            $table->softDeletes();

            $table->foreign('created_by_id')->references('id')->on('users');
            $table->foreign('modified_by_id')->references('id')->on('users')->onDelete('set null');
            $table->foreign('assigned_user_id')->references('id')->on('users');
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_contact_phone_table.php

<?php

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

class CreateContactPhoneTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('contact_phone', function (Blueprint $table) {
            $table->increments('id');
            $table->string('phone');
            $table->integer('contact_id')->unsigned();

            $table->timestamps();

            $table->foreign('contact_id')->references('id')->on('contact');
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_contact_email_table.php

<?php

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

class CreateContactEmailTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('contact_email', function (Blueprint $table) {
            $table->increments('id');
            $table->string('email');
            $table->integer('contact_id')->unsigned();

            $table->timestamps();

            $table->foreign('contact_id')->references('id')->on('contact');
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_contact_document_table.php

<?php

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

class CreateContactDocumentTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('contact_document', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('contact_id')->unsigned();
            $table->integer('document_id')->unsigned();

            $table->timestamps();

            $table->foreign('contact_id')->references('id')->on('contact');
            $table->foreign('document_id')->references('id')->on('document');
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_task_table.php

<?php

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

class CreateTaskTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('task', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('priority')->nullable()->comment('Low Normal High Urgent');
            $table->integer('status')->unsigned()->nullable();
            $table->integer('type_id')->unsigned();
            $table->string('start_date')->nullable();
            $table->string('end_date')->nullable();
            $table->string('complete_date')->nullable();
            $table->string('contact_type')->nullable()->comment('Lead Opportunity Customer Close');
            $table->integer('contact_id')->unsigned()->nullable();
            $table->text('description')->nullable();
            $table->integer('created_by_id')->unsigned();
            $table->integer('modified_by_id')->unsigned()->nullable();
            $table->integer('assigned_user_id')->unsigned()->nullable();

            $table->timestamps();
            $table->softDeletes();

            $table->foreign('status')->references('id')->on('task_status')->onDelete('set null');
            $table->foreign('type_id')->references('id')->on('task_type');
            $table->foreign('contact_id')->references('id')->on('contact')->onUpdate('set null')->onDelete('set null');
            $table->foreign('created_by_id')->references('id')->on('users');
            $table->foreign('modified_by_id')->references('id')->on('users')->onDelete('set null');
            $table->foreign('assigned_user_id')->references('id')->on('users');
        });
    }

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

database/migrations/XXXX_XX_XX_XXXXX_create_task_document_table.php

<?php

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

class CreateTaskDocumentTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('task_document', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('task_id')->unsigned();
            $table->integer('document_id')->unsigned();

            $table->timestamps();

            $table->foreign('task_id')->references('id')->on('task');
            $table->foreign('document_id')->references('id')->on('document');
        });
    }

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

Note that i have added two flags for the user migrations. The first is “is_admin” and this indicates that this is the super admin user in our case there will be one super admin user in the system. The second is “is_active” and this enables me to activate or deactivate a user.

Now run

php artisan migrate

Congratulations now the database tables created successfully. Now we will seed some static tables with some data, and what i mean with static tables is tables that don’t change frequently like contact_type

Seeding database with default data

Now we need to fill our tables with default data, such as creating a default user, settings, or static tables, etc. This data is important to control the system functionality on first start. We will do make the seed data available through a config file in the config/ directory.

Create a new config file in config/ directory to store seed data.

touch config/seed_data.php

Open config/seed_data.php and add the following:

<?php

// Database seeder data

return [
    'document_types' => ['Contract', 'License Agreement', 'EULA', 'Other'],
    'task_statuses' => ['Not Started', 'Started', 'Completed', 'Cancelled'],
    'task_types' => ['Task', 'Meeting', 'Phone call'],
    'contact_status' => ['Lead', 'Opportunity', 'Customer', 'Close'],
    'settings' => ['crm_email' => 'noreply@mini-crm.com', 'enable_email_notification' => 1],
    'permissions' => [
        'create_contact', 'edit_contact', 'delete_contact', 'list_contacts', 'view_contact', 'assign_contact',
        'create_document', 'edit_document', 'delete_document', 'list_documents', 'view_document', 'assign_document',
        'create_task', 'edit_task', 'delete_task', 'list_tasks', 'view_task', 'assign_task', 'update_task_status', 
        'edit_profile', 'compose_email', 'list_emails', 'view_email', 'toggle_important_email', 'trash_email', 'send_email',
        'reply_email', 'forward_email', 'show_email_notifications', 'show_calendar'
    ],
    'mailbox_folders' => array(
        array("title"=>"Inbox", "icon" => "fa fa-inbox"),
        array("title"=>"Sent", "icon" => "fa fa-envelope-o"),
        array("title"=>"Drafts", "icon" => "fa fa-file-text-o"),
        array("title"=>"Trash", "icon" => "fa fa-trash-o")
    )
];

Then open database/seeds/DatabaseSeeder.php and modify it as shown:

<?php

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        // $this->call(UsersTableSeeder::class);

        // inserting document types
        foreach (config('seed_data.document_types') as $value) {

            DB::table('document_type')->insert([
                'name' => $value
            ]);
        }

        // insert task status
        foreach (config('seed_data.task_statuses') as $value) {

            DB::table('task_status')->insert([
                'name' => $value
            ]);
        }

        // insert task types
        foreach (config('seed_data.task_types') as $value) {

            DB::table('task_type')->insert([
                'name' => $value
            ]);
        }

        // insert contact status
        foreach (config('seed_data.contact_status') as $value) {

            DB::table('contact_status')->insert([
                'name' => $value
            ]);
        }

        // insert the system main email into settings table
        foreach (config('seed_data.settings') as $key => $value) {
            DB::table('setting')->insert([
                'setting_key' => $key,
                'setting_value' => $value
            ]);
        }

        // insert sample user as the system admin
        DB::table('users')->insert([
           'name' => 'admin',
           'email' => 'admin@my-crm.com',
           'position_title' => 'sales manager',
           'is_admin' => 1,
           'is_active' => 1
        ]);

        // insert the initial permissions
        $permissions = [];
        foreach (config('seed_data.permissions') as $value) {
            $permissions[] = \Spatie\Permission\Models\Permission::create(['name' => $value]);
        }
    }
}

The run the seeder using this command

php artisan db:seed

Check your database tables you will see that data inserted successfully.

Continue to Part 3: Generating Models and Relations>>>