# Database

## 12.1 Database Schema

### Core Tables
The database schema is designed with normalization principles and optimized for performance.

```sql
-- Users table
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NULL,
    date_of_birth DATE NULL,
    gender ENUM('male', 'female', 'other') NULL,
    nationality VARCHAR(100) NULL,
    profile_image VARCHAR(255) NULL,
    payment_status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    must_change_password BOOLEAN DEFAULT FALSE,
    otp_code VARCHAR(6) NULL,
    otp_expires_at TIMESTAMP NULL,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

-- Tournaments table
CREATE TABLE tournaments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT NULL,
    location VARCHAR(255) NULL,
    venue VARCHAR(255) NULL,
    organizer VARCHAR(255) NULL,
    contact_email VARCHAR(255) NULL,
    contact_phone VARCHAR(255) NULL,
    website VARCHAR(255) NULL,
    type ENUM('knockout', 'round-robin', 'group-knockout', 'swiss_system') NOT NULL,
    status ENUM('draft', 'published', 'active', 'completed', 'cancelled') DEFAULT 'draft',
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    registration_start DATETIME NULL,
    registration_end DATETIME NULL,
    max_participants INT UNSIGNED NULL,
    min_participants INT UNSIGNED NULL,
    auto_accept_registrations BOOLEAN DEFAULT FALSE,
    registration_status ENUM('open', 'closed', 'full') DEFAULT 'open',
    game_title VARCHAR(255) NULL,
    game_version VARCHAR(100) NULL,
    format ENUM('team', 'individual') DEFAULT 'team',
    team_size INT UNSIGNED NULL,
    rounds INT UNSIGNED NULL,
    matches_per_round INT UNSIGNED NULL,
    bracket_settings JSON NULL,
    stream_url VARCHAR(255) NULL,
    youtube_url VARCHAR(255) NULL,
    twitch_url VARCHAR(255) NULL,
    is_streamed BOOLEAN DEFAULT FALSE,
    is_featured BOOLEAN DEFAULT FALSE,
    entry_fee DECIMAL(10,2) NULL,
    prize_pool DECIMAL(10,2) NULL,
    prize_distribution JSON NULL,
    currency VARCHAR(3) DEFAULT 'KES',
    sponsor_info JSON NULL,
    rewards JSON NULL,
    total_participants INT UNSIGNED DEFAULT 0,
    total_matches INT UNSIGNED DEFAULT 0,
    completed_matches INT UNSIGNED DEFAULT 0,
    average_match_duration INT UNSIGNED NULL,
    tournament_stats JSON NULL,
    banner_image VARCHAR(255) NULL,
    logo_image VARCHAR(255) NULL,
    social_links JSON NULL,
    announcement TEXT NULL,
    visibility ENUM('public', 'private') DEFAULT 'public',
    allow_spectators BOOLEAN DEFAULT TRUE,
    require_approval BOOLEAN DEFAULT TRUE,
    enable_two_tier_approval BOOLEAN DEFAULT FALSE,
    first_approver_id BIGINT UNSIGNED NULL,
    second_approver_id BIGINT UNSIGNED NULL,
    approval_settings JSON NULL,
    admin_notes TEXT NULL,
    metadata JSON NULL,
    rules TEXT NULL,
    settings JSON NULL,
    published_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (first_approver_id) REFERENCES users(id),
    FOREIGN KEY (second_approver_id) REFERENCES users(id),
    
    INDEX idx_tournaments_status (status),
    INDEX idx_tournaments_start_date (start_date),
    INDEX idx_tournaments_type (type),
    INDEX idx_tournaments_organizer (organizer)
);
```

### Team and Player Tables
```sql
-- Teams table
CREATE TABLE teams (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    logo VARCHAR(255) NULL,
    captain_id BIGINT UNSIGNED NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    founded_year YEAR NULL,
    location VARCHAR(255) NULL,
    contact_email VARCHAR(255) NULL,
    contact_phone VARCHAR(255) NULL,
    website VARCHAR(255) NULL,
    social_links JSON NULL,
    achievements JSON NULL,
    statistics JSON NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (captain_id) REFERENCES users(id),
    
    INDEX idx_teams_name (name),
    INDEX idx_teams_status (status),
    INDEX idx_teams_captain (captain_id)
);

-- Players table
CREATE TABLE players (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    team_id BIGINT UNSIGNED NULL,
    jersey_number INT UNSIGNED NULL,
    position ENUM('goalkeeper', 'defender', 'midfielder', 'forward') NULL,
    height INT UNSIGNED NULL,
    weight INT UNSIGNED NULL,
    preferred_foot ENUM('left', 'right', 'both') NULL,
    date_of_birth DATE NULL,
    nationality VARCHAR(100) NULL,
    id_number VARCHAR(50) NULL,
    phone VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    emergency_contact JSON NULL,
    medical_conditions TEXT NULL,
    profile_image VARCHAR(255) NULL,
    id_front_image VARCHAR(255) NULL,
    id_back_image VARCHAR(255) NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    otp_verified BOOLEAN DEFAULT FALSE,
    otp_code VARCHAR(6) NULL,
    otp_expires_at TIMESTAMP NULL,
    digital_id_generated BOOLEAN DEFAULT FALSE,
    statistics JSON NULL,
    achievements JSON NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (team_id) REFERENCES teams(id),
    
    INDEX idx_players_user (user_id),
    INDEX idx_players_team (team_id),
    INDEX idx_players_jersey (jersey_number),
    INDEX idx_players_position (position),
    INDEX idx_players_status (status)
);
```

### Match and Bracket Tables
```sql
-- Brackets table
CREATE TABLE brackets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    type ENUM('winners', 'losers', 'final') DEFAULT 'winners',
    rounds INT UNSIGNED DEFAULT 0,
    structure JSON NULL,
    seeding JSON NULL,
    is_active BOOLEAN DEFAULT TRUE,
    current_round INT UNSIGNED DEFAULT 1,
    status ENUM('pending', 'active', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id),
    
    INDEX idx_brackets_tournament (tournament_id),
    INDEX idx_brackets_type (type),
    INDEX idx_brackets_status (status)
);

-- Game matches table
CREATE TABLE game_matches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tournament_id BIGINT UNSIGNED NOT NULL,
    bracket_id BIGINT UNSIGNED NULL,
    round INT UNSIGNED NOT NULL,
    match_number INT UNSIGNED NOT NULL,
    team1_id BIGINT UNSIGNED NULL,
    team2_id BIGINT UNSIGNED NULL,
    player1_id BIGINT UNSIGNED NULL,
    player2_id BIGINT UNSIGNED NULL,
    status ENUM('scheduled', 'live', 'completed', 'cancelled', 'postponed') DEFAULT 'scheduled',
    match_type ENUM('team', 'individual', 'group', 'knockout', 'final') DEFAULT 'team',
    scheduled_at DATETIME NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    team1_score INT UNSIGNED DEFAULT 0,
    team2_score INT UNSIGNED DEFAULT 0,
    winner_id BIGINT UNSIGNED NULL,
    winning_team_id BIGINT UNSIGNED NULL,
    referee_id BIGINT UNSIGNED NULL,
    venue VARCHAR(255) NULL,
    notes TEXT NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id),
    FOREIGN KEY (bracket_id) REFERENCES brackets(id),
    FOREIGN KEY (team1_id) REFERENCES teams(id),
    FOREIGN KEY (team2_id) REFERENCES teams(id),
    FOREIGN KEY (player1_id) REFERENCES players(id),
    FOREIGN KEY (player2_id) REFERENCES players(id),
    FOREIGN KEY (winner_id) REFERENCES players(id),
    FOREIGN KEY (winning_team_id) REFERENCES teams(id),
    FOREIGN KEY (referee_id) REFERENCES users(id),
    
    INDEX idx_matches_tournament (tournament_id),
    INDEX idx_matches_bracket (bracket_id),
    INDEX idx_matches_round (round),
    INDEX idx_matches_status (status),
    INDEX idx_matches_scheduled (scheduled_at)
);
```

## 12.2 Migrations

### Migration Structure
```php
// Create tournaments table migration
<?php

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

return new class extends Migration
{
    public function up()
    {
        Schema::create('tournaments', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('slug')->unique();
            $table->text('description')->nullable();
            $table->string('location')->nullable();
            $table->string('venue')->nullable();
            $table->string('organizer')->nullable();
            $table->string('contact_email')->nullable();
            $table->string('contact_phone')->nullable();
            $table->string('website')->nullable();
            $table->enum('type', ['knockout', 'round-robin', 'group-knockout', 'swiss_system']);
            $table->enum('status', ['draft', 'published', 'active', 'completed', 'cancelled'])->default('draft');
            $table->datetime('start_date');
            $table->datetime('end_date');
            $table->datetime('registration_start')->nullable();
            $table->datetime('registration_end')->nullable();
            $table->unsignedInteger('max_participants')->nullable();
            $table->unsignedInteger('min_participants')->nullable();
            $table->boolean('auto_accept_registrations')->default(false);
            $table->enum('registration_status', ['open', 'closed', 'full'])->default('open');
            $table->string('game_title')->nullable();
            $table->string('game_version')->nullable();
            $table->enum('format', ['team', 'individual'])->default('team');
            $table->unsignedInteger('team_size')->nullable();
            $table->unsignedInteger('rounds')->nullable();
            $table->unsignedInteger('matches_per_round')->nullable();
            $table->json('bracket_settings')->nullable();
            $table->string('stream_url')->nullable();
            $table->string('youtube_url')->nullable();
            $table->string('twitch_url')->nullable();
            $table->boolean('is_streamed')->default(false);
            $table->boolean('is_featured')->default(false);
            $table->decimal('entry_fee', 10, 2)->nullable();
            $table->decimal('prize_pool', 10, 2)->nullable();
            $table->json('prize_distribution')->nullable();
            $table->string('currency', 3)->default('KES');
            $table->json('sponsor_info')->nullable();
            $table->json('rewards')->nullable();
            $table->unsignedInteger('total_participants')->default(0);
            $table->unsignedInteger('total_matches')->default(0);
            $table->unsignedInteger('completed_matches')->default(0);
            $table->unsignedInteger('average_match_duration')->nullable();
            $table->json('tournament_stats')->nullable();
            $table->string('banner_image')->nullable();
            $table->string('logo_image')->nullable();
            $table->json('social_links')->nullable();
            $table->text('announcement')->nullable();
            $table->enum('visibility', ['public', 'private'])->default('public');
            $table->boolean('allow_spectators')->default(true);
            $table->boolean('require_approval')->default(true);
            $table->boolean('enable_two_tier_approval')->default(false);
            $table->foreignId('first_approver_id')->nullable()->constrained('users');
            $table->foreignId('second_approver_id')->nullable()->constrained('users');
            $table->json('approval_settings')->nullable();
            $table->text('admin_notes')->nullable();
            $table->json('metadata')->nullable();
            $table->text('rules')->nullable();
            $table->json('settings')->nullable();
            $table->timestamp('published_at')->nullable();
            $table->timestamp('completed_at')->nullable();
            $table->timestamp('cancelled_at')->nullable();
            $table->timestamps();
            
            $table->index(['status', 'start_date']);
            $table->index('type');
            $table->index('organizer');
        });
    }

    public function down()
    {
        Schema::dropIfExists('tournaments');
    }
};
```

### Index Migrations
```php
// Add indexes for performance
<?php

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

return new class extends Migration
{
    public function up()
    {
        Schema::table('tournaments', function (Blueprint $table) {
            $table->index(['status', 'start_date'], 'idx_tournaments_status_start');
            $table->index(['type', 'status'], 'idx_tournaments_type_status');
            $table->index('organizer', 'idx_tournaments_organizer');
        });
        
        Schema::table('teams', function (Blueprint $table) {
            $table->index(['name', 'status'], 'idx_teams_name_status');
            $table->index('captain_id', 'idx_teams_captain');
        });
        
        Schema::table('players', function (Blueprint $table) {
            $table->index(['team_id', 'jersey_number'], 'idx_players_team_jersey');
            $table->index(['position', 'status'], 'idx_players_position_status');
        });
        
        Schema::table('game_matches', function (Blueprint $table) {
            $table->index(['tournament_id', 'round'], 'idx_matches_tournament_round');
            $table->index(['status', 'scheduled_at'], 'idx_matches_status_scheduled');
            $table->index(['team1_id', 'team2_id'], 'idx_matches_teams');
        });
    }

    public function down()
    {
        Schema::table('tournaments', function (Blueprint $table) {
            $table->dropIndex('idx_tournaments_status_start');
            $table->dropIndex('idx_tournaments_type_status');
            $table->dropIndex('idx_tournaments_organizer');
        });
        
        Schema::table('teams', function (Blueprint $table) {
            $table->dropIndex('idx_teams_name_status');
            $table->dropIndex('idx_teams_captain');
        });
        
        Schema::table('players', function (Blueprint $table) {
            $table->dropIndex('idx_players_team_jersey');
            $table->dropIndex('idx_players_position_status');
        });
        
        Schema::table('game_matches', function (Blueprint $table) {
            $table->dropIndex('idx_matches_tournament_round');
            $table->dropIndex('idx_matches_status_scheduled');
            $table->dropIndex('idx_matches_teams');
        });
    }
};
```

## 12.3 Seeders

### Database Seeders
```php
// DatabaseSeeder
<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run()
    {
        $this->call([
            RoleSeeder::class,
            PermissionSeeder::class,
            UserSeeder::class,
            TournamentSeeder::class,
            TeamSeeder::class,
            PlayerSeeder::class,
        ]);
    }
}

// RoleSeeder
class RoleSeeder extends Seeder
{
    public function run()
    {
        $roles = [
            ['name' => 'super_admin', 'display_name' => 'Super Admin', 'description' => 'Full system access'],
            ['name' => 'admin', 'display_name' => 'Admin', 'description' => 'System administration'],
            ['name' => 'organizer', 'display_name' => 'Tournament Organizer', 'description' => 'Tournament management'],
            ['name' => 'team_manager', 'display_name' => 'Team Manager', 'description' => 'Team management'],
            ['name' => 'player', 'display_name' => 'Player', 'description' => 'Player access'],
            ['name' => 'referee', 'display_name' => 'Referee', 'description' => 'Match management'],
            ['name' => 'official', 'display_name' => 'Official', 'description' => 'Tournament operations'],
        ];

        foreach ($roles as $role) {
            Role::create($role);
        }
    }
}

// PermissionSeeder
class PermissionSeeder extends Seeder
{
    public function run()
    {
        $permissions = [
            // Tournament permissions
            ['name' => 'tournaments.create', 'display_name' => 'Create Tournaments', 'category' => 'tournaments'],
            ['name' => 'tournaments.view', 'display_name' => 'View Tournaments', 'category' => 'tournaments'],
            ['name' => 'tournaments.edit', 'display_name' => 'Edit Tournaments', 'category' => 'tournaments'],
            ['name' => 'tournaments.delete', 'display_name' => 'Delete Tournaments', 'category' => 'tournaments'],
            
            // Team permissions
            ['name' => 'teams.create', 'display_name' => 'Create Teams', 'category' => 'teams'],
            ['name' => 'teams.view', 'display_name' => 'View Teams', 'category' => 'teams'],
            ['name' => 'teams.edit', 'display_name' => 'Edit Teams', 'category' => 'teams'],
            ['name' => 'teams.delete', 'display_name' => 'Delete Teams', 'category' => 'teams'],
            ['name' => 'teams.approve', 'display_name' => 'Approve Teams', 'category' => 'teams'],
            
            // Player permissions
            ['name' => 'players.create', 'display_name' => 'Create Players', 'category' => 'players'],
            ['name' => 'players.view', 'display_name' => 'View Players', 'category' => 'players'],
            ['name' => 'players.edit', 'display_name' => 'Edit Players', 'category' => 'players'],
            ['name' => 'players.delete', 'display_name' => 'Delete Players', 'category' => 'players'],
            
            // Match permissions
            ['name' => 'matches.create', 'display_name' => 'Create Matches', 'category' => 'matches'],
            ['name' => 'matches.view', 'display_name' => 'View Matches', 'category' => 'matches'],
            ['name' => 'matches.edit', 'display_name' => 'Edit Matches', 'category' => 'matches'],
            ['name' => 'matches.delete', 'display_name' => 'Delete Matches', 'category' => 'matches'],
            ['name' => 'matches.start', 'display_name' => 'Start Matches', 'category' => 'matches'],
            ['name' => 'matches.complete', 'display_name' => 'Complete Matches', 'category' => 'matches'],
        ];

        foreach ($permissions as $permission) {
            Permission::create($permission);
        }
    }
}
```

## 12.4 Relationships

### Model Relationships
```php
// User relationships
class User extends Authenticatable
{
    public function teams() { return $this->hasMany(Team::class, 'captain_id'); }
    public function tournaments() { return $this->hasMany(Tournament::class, 'organizer_id'); }
    public function payments() { return $this->hasMany(Payment::class); }
    public function roles() { return $this->belongsToMany(Role::class); }
    public function player() { return $this->hasOne(Player::class); }
}

// Tournament relationships
class Tournament extends Model
{
    public function brackets() { return $this->hasMany(Bracket::class); }
    public function gameMatches() { return $this->hasMany(GameMatch::class); }
    public function teams() { return $this->belongsToMany(Team::class); }
    public function organizer() { return $this->belongsTo(User::class, 'organizer_id'); }
    public function firstApprover() { return $this->belongsTo(User::class, 'first_approver_id'); }
    public function secondApprover() { return $this->belongsTo(User::class, 'second_approver_id'); }
}

// Team relationships
class Team extends Model
{
    public function captain() { return $this->belongsTo(User::class, 'captain_id'); }
    public function players() { return $this->hasMany(Player::class); }
    public function teamOfficials() { return $this->hasMany(TeamOfficial::class); }
    public function tournaments() { return $this->belongsToMany(Tournament::class); }
    public function formations() { return $this->hasMany(Formation::class); }
}

// Player relationships
class Player extends Model
{
    public function user() { return $this->belongsTo(User::class); }
    public function team() { return $this->belongsTo(Team::class); }
    public function matches() { return $this->hasMany(GameMatch::class, 'player1_id'); }
    public function statistics() { return $this->hasOne(PlayerStatistics::class); }
}
```

### Pivot Tables
```php
// Tournament-Team pivot table
Schema::create('tournament_team', function (Blueprint $table) {
    $table->id();
    $table->foreignId('tournament_id')->constrained()->onDelete('cascade');
    $table->foreignId('team_id')->constrained()->onDelete('cascade');
    $table->enum('status', ['pending', 'approved', 'rejected'])->default('pending');
    $table->timestamp('approved_at')->nullable();
    $table->foreignId('approved_by')->nullable()->constrained('users');
    $table->text('rejection_reason')->nullable();
    $table->timestamps();
    
    $table->unique(['tournament_id', 'team_id']);
    $table->index(['tournament_id', 'status']);
    $table->index(['team_id', 'status']);
});

// User-Role pivot table
Schema::create('user_role', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->foreignId('role_id')->constrained()->onDelete('cascade');
    $table->timestamps();
    
    $table->unique(['user_id', 'role_id']);
});

// Role-Permission pivot table
Schema::create('role_permission', function (Blueprint $table) {
    $table->id();
    $table->foreignId('role_id')->constrained()->onDelete('cascade');
    $table->foreignId('permission_id')->constrained()->onDelete('cascade');
    $table->timestamps();
    
    $table->unique(['role_id', 'permission_id']);
});
```

## 12.5 Indexes

### Performance Indexes
```sql
-- Primary indexes for performance
CREATE INDEX idx_tournaments_status_start ON tournaments(status, start_date);
CREATE INDEX idx_tournaments_type_status ON tournaments(type, status);
CREATE INDEX idx_tournaments_organizer ON tournaments(organizer);

CREATE INDEX idx_teams_name_status ON teams(name, status);
CREATE INDEX idx_teams_captain ON teams(captain_id);

CREATE INDEX idx_players_team_jersey ON players(team_id, jersey_number);
CREATE INDEX idx_players_position_status ON players(position, status);
CREATE INDEX idx_players_user ON players(user_id);

CREATE INDEX idx_matches_tournament_round ON game_matches(tournament_id, round);
CREATE INDEX idx_matches_status_scheduled ON game_matches(status, scheduled_at);
CREATE INDEX idx_matches_teams ON game_matches(team1_id, team2_id);

CREATE INDEX idx_payments_user_status ON payments(user_id, status);
CREATE INDEX idx_payments_team_status ON payments(team_id, status);
CREATE INDEX idx_payments_created ON payments(created_at);
```

### Composite Indexes
```sql
-- Composite indexes for complex queries
CREATE INDEX idx_tournaments_organizer_status ON tournaments(organizer_id, status);
CREATE INDEX idx_teams_captain_status ON teams(captain_id, status);
CREATE INDEX idx_players_team_position ON players(team_id, position);
CREATE INDEX idx_matches_tournament_status ON game_matches(tournament_id, status);
```

## 12.6 Performance

### Query Optimization
```php
// Eager loading to prevent N+1 queries
$tournaments = Tournament::with([
    'teams',
    'brackets.gameMatches',
    'organizer'
])->get();

// Specific field selection
$tournaments = Tournament::select([
    'id', 'name', 'status', 'start_date', 'end_date'
])->where('status', 'active')->get();

// Query scopes for common filters
class Tournament extends Model
{
    public function scopeActive($query)
    {
        return $query->where('status', 'active');
    }
    
    public function scopeByOrganizer($query, $organizerId)
    {
        return $query->where('organizer_id', $organizerId);
    }
    
    public function scopeUpcoming($query)
    {
        return $query->where('start_date', '>', now());
    }
}

// Usage
$activeTournaments = Tournament::active()
    ->byOrganizer($userId)
    ->upcoming()
    ->get();
```

### Database Configuration
```php
// Database configuration for performance
'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'tournament_management'),
        'username' => env('DB_USERNAME', 'root'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => 'InnoDB',
        'options' => [
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
        ],
    ],
],
```

### Caching Strategy
```php
// Model caching
class Tournament extends Model
{
    protected $fillable = ['name', 'status', 'start_date'];
    
    public function getCachedTournaments()
    {
        return Cache::remember('tournaments.active', 3600, function () {
            return $this->where('status', 'active')->get();
        });
    }
}

// Query result caching
$tournaments = Cache::remember('tournaments.list', 1800, function () {
    return Tournament::with(['teams', 'organizer'])
        ->where('status', 'active')
        ->orderBy('start_date')
        ->get();
});
```

---

## Database Best Practices

### Design Principles
- **Normalization**: Proper database normalization
- **Indexing**: Strategic index placement
- **Relationships**: Proper foreign key relationships
- **Constraints**: Data integrity constraints
- **Performance**: Query optimization

### Security Measures
- **Access Control**: Database user permissions
- **Encryption**: Sensitive data encryption
- **Backup**: Regular database backups
- **Monitoring**: Database performance monitoring
- **Auditing**: Database access auditing

---

## Next Steps

- [Configuration](13_Configuration.md) - Database configuration
- [Deployment](14_Deployment.md) - Production database setup
- [Security](11_Security.md) - Database security
- [Maintenance](16_Maintenance.md) - Database maintenance
