Backend Developmentcrm

Implementing CRM System With Laravel Part 2: Database

implementing crm with laravel database

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:

 

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:

crm contacts table

contacts table

crm erd diagram

contact phones and contact emails

crm erd diagram

contact document and contact status

crm erd diagram

task and task related tables

crm users table

users table

crm erd diagram

document tables

crm erd diagram

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

0 0 vote
Article Rating

What's your reaction?

Excited
0
Happy
0
Not Sure
0
Confused
0

You may also like

Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
syed bilawal bukhari
syed bilawal bukhari
6 months ago

i am getting error when i run php artisan migrate after performing each step carefully:  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘crm_email’ for key ‘setting_setting_key_unique’ (SQL: insert into setting (setting_key, setting_value) values (crm_email, noreply@mini-crm.com))  at F:\old computer\laravel\Mechanic_CRM\vendor\laravel\framework\src\Illuminate\Database\Connection.php:671   667▕     // If an exception occurs when attempting to run a query, we’ll format the error   668▕     // message to include the bindings with SQL, which will make this exception a   669▕     // lot more helpful to the developer instead of just the database’s errors.   670▕     catch (Exception $e) {  ➜ 671▕       throw new QueryException(   672▕         $query, $this->prepareBindings($bindings), $e   673▕       );   674▕     }   675▕  1  F:\old computer\laravel\Mechanic_CRM\vendor\laravel\framework\src\Illuminate\Database\Connection.php:464    PDOException::(“SQLSTATE[23000]: Integrity constraint violation:… Read more »