Data Model and Migrations (Selected Highlights) – Recent Domain Evolutions

This section spotlights key schema changes reflecting recent product iterations. We cover the Top Players pivot evolution, College table enhancements, and new Event fields. Each migration ensures data integrity and supports emerging features.


🆕 Top Players Pivot Evolution

The top_players pivot table tracks standout athletes per team—and now per event. Recent migrations expanded its scope, adjusted constraints, and enforced referential integrity.

  • Initial creation of the pivot table with unique team–player pairs.
  • Extension by attaching events, making entries nullable and re-unique’ing on event–team–player.
  • Cascade deletes to automatically clean up on related deletions.
Migration File Purpose
2025_05_07_114430_create_top_players_table.php Create top_players with team_id, player_id and a unique index
2025_08_22_100803_add_event_id_to_top_players_table.php Add nullable event_id, modify constraints, re-unique on three columns
// Create top_players table
Schema::create('top_players', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
    $table->foreignId('team_id')->constrained('teams')->onDelete('cascade');
    $table->foreignId('player_id')->constrained('players')->onDelete('cascade');
    $table->unique(['team_id', 'player_id']);
});
// Extend pivot to include event context
Schema::table('top_players', function (Blueprint $table) {
    if (! Schema::hasColumn('top_players', 'event_id')) {
        $table->foreignId('event_id')->nullable()->constrained('events');
    }
    // Drop old FKs and unique before altering columns...
    $table->dropForeign(['team_id']);
    $table->dropForeign(['player_id']);
    $table->dropUnique(['team_id', 'player_id']);
    $table->foreignId('team_id')->nullable()->change();
    $table->foreignId('player_id')->nullable()->change();
    $table->foreign('team_id')->references('id')->on('teams')->onDelete('cascade');
    $table->foreign('player_id')->references('id')->on('players')->onDelete('cascade');
    $table->unique(['event_id', 'player_id', 'team_id']);
});
erDiagram
    TEAMS ||--o{ TOP_PLAYERS : contains
    PLAYERS ||--o{ TOP_PLAYERS : features
    EVENTS ||--o{ TOP_PLAYERS : context
{
    "title": "Data Integrity",
    "content": "Cascade deletes and multi-column unique constraints prevent orphaned or duplicate pivot entries."
}

🏫 College Table Enhancements

Colleges now include a registration flag and a Prep Network ID to power RSVP flows and external lookups.

  • registered boolean defaults true for legacy data.
  • prepnetwork_id stores external identifiers for integration.
  • Ensures smooth transition for existing records.
Migration File Change
2025_03_24_154733_add_registered_column_to_colleges_table.php Add registered boolean, default false; seed existing rows to true
2025_05_09_095454_add_prepnetwork_id_to_colleges_table.php Add nullable prepnetwork_id string
// Add registration flag
Schema::table('colleges', function (Blueprint $table) {
    $table->boolean('registered')->default(false);
});
// Backfill all existing colleges
\DB::table('colleges')->update(['registered' => true]);
// Add external Prep Network ID
Schema::table('colleges', function (Blueprint $table) {
    $table->string('prepnetwork_id')->nullable();
});

⚙️ Event Table Iterations

The events table continues to evolve with JSON support and custom limits for registrations and pricing.

  • max_registration_by_position: per-position caps for flexible team sizing.
  • Other JSON/Boolean fields added earlier (not in this highlight).
  • Enables front-end widgets to enforce granular limits.
Migration File Field
2025_05_09_135250_add_max_registrations_by_position_field_to_events.php json max_registration_by_position
// Store per-position registration caps
Schema::table('events', function (Blueprint $table) {
    $table->json('max_registration_by_position')->nullable();
});

Summary: These migrations demonstrate ongoing product iteration: evolving pivots for richer relationships, augmenting core tables for new features, and leveraging JSON fields for flexible data storage. Proper use of nullable, default, and unique ensures backward compatibility and data integrity throughout.