Project Board Domain Modeling: Database Design and Eloquent

Previously in this course, we explored Repository Pattern Fundamentals to decouple our data access layer from our business logic. In this lesson, we shift our focus to the foundation of that layer: the database schema. We'll design the core entities for our multi-user project board—Users, Projects, and Tasks—and establish the relational integrity required for a production application.

The Problem: Beyond Simple CRUD

When starting a project, it's tempting to throw tables together without considering how they interact. However, a production-grade application requires strict constraints to prevent orphaned records and inconsistent states. We aren't just storing data; we are modeling a domain.

For our project board, we have three primary entities:

1. Users: The owners and contributors.
2. Projects: Containers for tasks, owned by a single user (for now).
3. Tasks: Actionable items belonging to a project.

Designing the Schema with Migrations

We will use Laravel's migration system to enforce these relationships at the database level. While Eloquent handles the "what" in our code, the database schema handles the "how" of data integrity.

1. The Projects Table

A project must belong to a user. We'll use a foreign key constraint to ensure that if a user is deleted, their projects are handled according to our business rules (e.g., onDelete('cascade')).

PHP
Schema::create('projects', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->string('name');
    $table->text('description')->nullable();
    $table->timestamps();
});

2. The Tasks Table

Tasks belong to a project. By enforcing the project_id foreign key, we guarantee that no task can exist in a vacuum.

PHP
Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->foreignId('project_id')->constrained()->onDelete('cascade');
    $table->string('title');
    $table->boolean('is_completed')->default(false);
    $table->timestamps();
});

Establishing Eloquent Relationships

Once the schema is defined, we map these relationships in our models. This allows us to traverse the graph of data easily. For a refresher on these basics, see Introduction to Database Relationships in Laravel.

In the Project model:

PHP
public function tasks(): HasMany
{
    return $this->hasMany(Task::class);
}

public function owner(): BelongsTo
{
    return $this->belongsTo(User::class, 'user_id');
}

In the Task model:

PHP
public function project(): BelongsTo
{
    return $this->belongsTo(Project::class);
}

Hands-on Exercise

Your task is to extend the schema to support a "priority" level for tasks.

1. Create a migration to add an integer column named priority to the tasks table with a default value of 0.
2. Update your Task model to ensure this field is mass-assignable via the $fillable array.
3. If you're looking for inspiration on how to manage these attributes, check out Task Manager: Adding Status and Priorities in Laravel.

Common Pitfalls

• Missing Foreign Keys: Never skip constrained() in your migrations. Without it, you lose database-level integrity, making it possible to have "orphaned" tasks that point to non-existent projects.
• Over-reliance on Cascades: While onDelete('cascade') is convenient, be careful. In some production systems, you might prefer onDelete('restrict') to prevent accidental deletion of a project that still contains active tasks.
• Ignoring Indexing: Foreign keys in Laravel automatically create an index, but as your project grows, you'll eventually need to optimize queries further by adding composite indexes.

Recap

We've moved from abstract requirements to a concrete database structure. By using migrations to define foreign key constraints, we've ensured that our data remains consistent. By mapping these in Eloquent, we’ve prepared our application to handle complex queries efficiently. This domain modeling approach is the bedrock of maintainable Laravel applications.

Up next: We will dive into Advanced Eloquent Scopes and Accessors to keep our queries clean and our model data formatted for the API.