Building the Backend: Database & Models

Introduction

With the project setup complete, we now move into backend development, starting with database schema design, model creation, and query optimization. A well-structured database ensures efficient data retrieval, scalability, and maintainability for our Laravel dashboard. This chapter will cover choosing a database, setting up migrations, defining Eloquent models, seeding test data, and optimizing queries.

---

Designing the Database Schema

1. Choosing a Database: MySQL, PostgreSQL, or MongoDB

Most Laravel dashboards use relational databases like MySQL or PostgreSQL. Choosing the right one depends on project requirements:

• MySQL: Widely used, easier to set up, good for standard applications.
• PostgreSQL: Advanced features like JSON columns, GIS support, full-text search, and better handling of complex queries.

To configure Laravel for a chosen database, update .env and config/database.php:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=dashboard_db
DB_USERNAME=root
DB_PASSWORD=secret

For PostgreSQL, change DB_CONNECTION=pgsql.

2. When to Consider MongoDB or NoSQL

If handling large, schema-less datasets (e.g., logs, analytics events), MongoDB could be useful. Laravel doesn’t support it natively, but the Jenssegers Laravel-MongoDB package allows integration.

---

Implementing Schema with Migrations

1. Creating Migrations

Laravel migrations keep database changes version-controlled. Example migration for users and orders:

php artisan make:migration create_orders_table

Inside database/migrations, define table schema:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->decimal('total_price', 10, 2);
    $table->timestamps();
});

Run migrations:

php artisan migrate

2. Rolling Back and Modifying Migrations

• php artisan migrate:rollback (undo last migration)
• php artisan migrate:fresh (reset database)
• Always use new migrations to modify existing tables, instead of editing old ones.

---

Implementing Eloquent Models and Relationships

1. Creating Models

Generate models with:

php artisan make:model Order

Set mass-assignable fields ($fillable) and casts:

class Order extends Model {
    protected $fillable = ['user_id', 'total_price'];
    protected $casts = ['total_price' => 'decimal:2'];
}

2. Defining Relationships

One-to-Many Example (User -> Orders)

In User model:

public function orders() {
    return $this->hasMany(Order::class);
}

In Order model:

public function user() {
    return $this->belongsTo(User::class);
}

Usage: $user->orders retrieves all orders for a user.

Many-to-Many Example (User -> Roles)

Pivot table role_user needed:

public function roles() {
    return $this->belongsToMany(Role::class);
}

Assign a role to a user:

$user->roles()->attach($roleId);

Polymorphic Example (Comments on Users and Orders)

public function commentable() {
    return $this->morphTo();
}

Usage: $comment->commentable retrieves the related entity.

---

Factories & Seeders for Test Data

1. Creating Factories

Generate factory:

php artisan make:factory OrderFactory --model=Order

Define fake data using Faker:

public function definition() {
    return [
        'user_id' => User::factory(),
        'total_price' => $this->faker->randomFloat(2, 10, 500),
    ];
}

2. Running Seeders

Generate seeder:

php artisan make:seeder OrderSeeder

Define seeder:

public function run() {
    Order::factory()->count(50)->create();
}

Run all seeders:

php artisan db:seed

---

Optimizing Queries and Indexing for Large Datasets

1. Preventing N+1 Queries with Eager Loading

Instead of:

$orders = Order::all();
foreach ($orders as $order) {
    echo $order->user->name;
}

Use eager loading:

$orders = Order::with('user')->get();

Reduces database queries from N+1 to just 2.

2. Query Optimization

• Use select() to fetch only needed columns:

Order::select('id', 'total_price')->get();

• Use chunk() for large datasets:

Order::chunk(100, function ($orders) {
    // Process orders in batches
});

3. Database Indexing

Add indexes to frequently queried fields:

$table->index('email');
$table->index(['status', 'created_at']);

4. Analyzing Performance

• Use Laravel Debugbar or Telescope to monitor queries.
• Run EXPLAIN in SQL to check how queries use indexes.

5. Caching Heavy Queries

$topUsers = Cache::remember('top_users', 600, function() {
    return User::orderBy('orders_count', 'desc')->take(10)->get();
});

This caches query results in Redis/Memcached for 10 minutes.

6. Handling Large Datasets

• Archive old data (e.g., move year-old orders to an archive table).
• Use a data warehouse for historical analytics.

---

Summary

By the end of this chapter, we have: ✅ Designed and implemented the database schema. ✅ Created Laravel migrations and models. ✅ Defined Eloquent relationships. ✅ Generated test data with factories and seeders. ✅ Applied query optimizations and indexing strategies.

---

Quiz – Database & Eloquent

1. In Laravel’s Eloquent, how do you define a one-to-many relationship between Author and Post models?
2. What is the N+1 query problem, and how do you solve it in Laravel?
3. Why are database indexes important, and when should you add an index to a column?

Completing this chapter ensures your Laravel dashboard has a highly efficient and structured backend, setting the stage for front-end development in the next section.