Creating a MySQL Database Schema for Email Marketing Automation Tool with Tags, Automation Workflow, and User Roles in Laravel

here’s an example of how you can create the necessary database tables in MySQL for an email marketing automation tool with tags and automation workflow using Laravel migrations with customer and admin roles:

1. Create a migration for the Users table:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->string('password');
    $table->enum('role', ['customer', 'admin'])->default('customer');
    $table->rememberToken();
    $table->timestamps();
});

2. Create a migration for the Contacts table:

Schema::create('contacts', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamps();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

3. Create a migration for the Tags table:

Schema::create('tags', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('name')->unique();
    $table->timestamps();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

4. Create a migration for the ContactTags pivot table:

Schema::create('contact_tags', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('contact_id');
    $table->unsignedBigInteger('tag_id');
    $table->foreign('contact_id')->references('id')->on('contacts')->onDelete('cascade');
    $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
    $table->timestamps();
});

5. Create a migration for the Lists table:

Schema::create('lists', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('name');
    $table->timestamps();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

6. Create a migration for the ListContacts pivot table:

Schema::create('list_contacts', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('list_id');
    $table->unsignedBigInteger('contact_id');
    $table->foreign('list_id')->references('id')->on('lists')->onDelete('cascade');
    $table->foreign('contact_id')->references('id')->on('contacts')->onDelete('cascade');
    $table->timestamps();
});

7. Create a migration for the Campaigns table:

Schema::create('campaigns', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('name');
    $table->timestamps();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

8. Create a migration for the CampaignContacts pivot table:

Schema::create('campaign_contacts', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('campaign_id');
    $table->unsignedBigInteger('contact_id');
    $table->foreign('campaign_id')->references('id')->on('campaigns')->onDelete('cascade);
$table->foreign('contact_id')->references('id')->on('contacts')->onDelete('cascade');
$table->timestamps();
});

9. Create a migration for the `Emails` table:

Schema::create('emails', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('name');
    $table->text('content');
    $table->timestamps();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

10. Create a migration for the Automations table:

Schema::create('automations', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('name');
    $table->boolean('active')->default(false);
    $table->timestamps();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});

11. Create a migration for the AutomationSteps table:

Schema::create('automation_steps', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('automation_id');
    $table->string('type');
    $table->text('parameters')->nullable();
    $table->integer('order');
    $table->timestamps();
    $table->foreign('automation_id')->references('id')->on('automations')->onDelete('cascade');
});

12. Create a migration for the AutomationLogs table:

Schema::create('automation_logs', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('automation_id');
    $table->unsignedBigInteger('contact_id');
    $table->unsignedBigInteger('step_id');
    $table->boolean('completed')->default(false);
    $table->text('data')->nullable();
    $table->timestamps();
    $table->foreign('automation_id')->references('id')->on('automations')->onDelete('cascade');
    $table->foreign('contact_id')->references('id')->on('contacts')->onDelete('cascade');
    $table->foreign('step_id')->references('id')->on('automation_steps')->onDelete('cascade');
});