implementing crm with laravel database

Implementing CRM System With Laravel Part 2: 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:

Next change your database settings in .env

 

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

database/migrations/XXXX_XX_XX_XXXXX_create_users_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_setting_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_document_type_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_document_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_task_status_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_task_type_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_contact_status_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_contact_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_contact_phone_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_contact_email_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_contact_document_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_task_table.php

database/migrations/XXXX_XX_XX_XXXXX_create_task_document_table.php

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

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.

Open config/seed_data.php and add the following:

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

The run the seeder using this command

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

 

Continue to Part 3: Generating Models and Relations

Share this: