💻 Database Standards

Professional database design and management standards for Laravel applications. This section covers migration patterns, model relationships, query optimization, and performance best practices.

📋 Migration Patterns

Migration Naming Conventions

Migrations should follow a clear naming pattern that describes the action and table.

// Good: Descriptive migration names
2024_01_15_000001_create_users_table.php
2024_01_15_000002_create_posts_table.php
2024_01_15_000003_add_email_verified_at_to_users_table.php

Migration Structure

✅ Good Example

<?php

declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->boolean('is_active')->default(true);
            $table->string('role')->default('user');
            $table->timestamps();
            
            // Add indexes for better performance
            $table->index(['email', 'is_active']);
            $table->index('role');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

❌ Bad Example

<?php

// Missing strict types declaration
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        // No return type declaration
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email'); // Missing unique constraint
            $table->string('password');
            $table->timestamps();
            // Missing indexes for performance
            // Missing important fields like email verification
        });
    }

    public function down()
    {
        // No return type declaration
        Schema::drop('users'); // Should use dropIfExists for safety
    }
};

🔗 Model Relationships

Define relationships clearly with proper foreign keys and constraints.

✅ Good Example

<?php

declare(strict_types=1);

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class User extends Model
{
    protected $fillable = [
        'name',
        'email',
        'password',
        'role_id',
    ];

    protected $hidden = [
        'password',
        'remember_token',
    ];

    protected $casts = [
        'email_verified_at' => 'datetime',
        'password' => 'hashed',
    ];

    public function posts(): HasMany
    {
        return $this->hasMany(Post::class);
    }

    public function role(): BelongsTo
    {
        return $this->belongsTo(Role::class);
    }

    public function permissions(): BelongsToMany
    {
        return $this->belongsToMany(Permission::class, 'user_permissions');
    }

    public function activePosts(): HasMany
    {
        return $this->hasMany(related: Post::class)->where(column: 'is_published', operator: '=', value: true);
    }
}

❌ Bad Example

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    // Missing fillable array - security risk
    // Missing hidden fields
    // Missing casts

    public function posts()
    {
        // Missing return type declaration
        return $this->hasMany(Post::class);
    }

    public function role()
    {
        // Missing return type declaration
        return $this->belongsTo(Role::class);
    }

    // Missing proper relationship definitions
    // No scoped relationships
    // No proper foreign key specifications
}

⚡ Query Optimization

Eager Loading

✅ Good Example

<?php

// Eager load relationships to avoid N+1 queries
$users = User::with(relations: ['posts', 'role', 'permissions'])
    ->where(column: 'is_active', operator: '=', value: true)
    ->orderBy(column: 'created_at', direction: 'desc')
    ->paginate(perPage: 20);

// Use specific columns when possible
$users = User::select(columns: ['id', 'name', 'email', 'role_id'])
    ->with(relations: ['role:id,name'])
    ->get();

❌ Bad Example

<?php

// N+1 query problem
$users = User::where(column: 'is_active', operator: '=', value: true)->get();

foreach ($users as $user) {
    echo $user->posts->count(); // This will cause N+1 queries
    echo $user->role->name; // This will also cause N+1 queries
}

Database Indexes

✅ Good Example

<?php

// Add indexes for frequently queried columns
Schema::table('users', function (Blueprint $table) {
    $table->index(['email', 'is_active']); // Composite index
    $table->index('role_id'); // Foreign key index
    $table->index('created_at'); // Date range queries
});

❌ Bad Example

<?php

// No indexes - will cause slow queries
Schema::table('users', function (Blueprint $table) {
    $table->string('email'); // No index on frequently queried field
    $table->unsignedBigInteger('role_id'); // No foreign key index
    $table->timestamp('created_at'); // No index for date queries
});

🎯 Best Practices

✅ Do's

• Use Migrations for all database changes
• Add Proper Indexes for performance
• Define Relationships with return types
• Use Eager Loading to prevent N+1 queries
• Implement Soft Deletes when appropriate
• Use Database Transactions for complex operations
• Validate Data at both application and database level
• Use Proper Naming conventions for tables and columns

❌ Don'ts

• Don't modify production databases directly
• Don't skip migration rollbacks
• Don't ignore foreign key constraints
• **Don't use SELECT *** in production queries
• Don't forget to add indexes on frequently queried columns
• Don't mix business logic in model relationships
• Don't ignore database normalization
• Don't skip data validation

🔧 Query Performance Tips

Use Database Indexes Strategically

<?php

// Migration: Add indexes for frequently queried columns
Schema::table('orders', function (Blueprint $table) {
    // Composite index for status and date queries
    $table->index(['status', 'created_at'], 'idx_orders_status_date');
    
    // Foreign key indexes
    $table->index('user_id', 'idx_orders_user');
    $table->index('product_id', 'idx_orders_product');
    
    // Full-text search index
    $table->fullText(['title', 'description'], 'idx_orders_fulltext');
});

Optimize Complex Queries

<?php

// Instead of loading all relationships
$users = User::with(['posts', 'comments', 'profile', 'settings'])->get();

// Load only what you need
$users = User::with(['posts:id,user_id,title', 'profile:id,user_id,bio'])
    ->select(['id', 'name', 'email'])
    ->get();

// Use lazy eager loading when needed
$users = User::all();
if ($needPosts) {
    $users->load(relations: 'posts');
}

Use Database Transactions

<?php

use Illuminate\Support\Facades\DB;

public function transferFunds(User $from, User $to, int $amount): bool
{
    return DB::transaction(function () use ($from, $to, $amount) {
        // Deduct from sender
        $from->decrement('balance', $amount);
        
        // Add to recipient
        $to->increment('balance', $amount);
        
        // Log transaction
        Transaction::create([
            'from_user_id' => $from->id,
            'to_user_id' => $to->id,
            'amount' => $amount,
        ]);
        
        return true;
    });
}

Repository Pattern

For comprehensive data access patterns and repository implementation, see Design Patterns.

---

📝 Database Performance: Regularly monitor query performance and optimize slow queries. Use Laravel's query logging and database profiling tools.