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.
registeredboolean defaults true for legacy data.prepnetwork_idstores 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.