Normalize the `Player` model by extracting `Position`, `Team`, and `League` into separate tables

[nanotaboada]

Description

Refactor the Player model to follow database normalization practices. Extract Position, Team, and League into their own tables, reducing data redundancy and improving data integrity.

ERD

DDL

CREATE TABLE IF NOT EXISTS "leagues" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL UNIQUE,
    "country" TEXT NOT NULL,
    "confederation" TEXT
);

CREATE TABLE IF NOT EXISTS "teams" (
    -- https://liaison.reuters.com/tools/sports-team-codes
    "id" TEXT PRIMARY KEY,
    "name" TEXT NOT NULL UNIQUE,
    "leagueId" INTEGER NOT NULL,
    FOREIGN KEY ("leagueId") REFERENCES "leagues"("id") ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS "positions" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "name" TEXT NOT NULL,
    CHECK (
        id = 'GK' AND name = 'Goalkeeper'
        OR id = 'RB' AND name = 'Right-Back'
        OR id = 'LB' AND name = 'Left-Back'
        OR id = 'CB' AND name = 'Centre-Back'
        OR id = 'DM' AND name = 'Defensive Midfield'
        OR id = 'CM' AND name = 'Central Midfield'
        OR id = 'RW' AND name = 'Right Winger'
        OR id = 'AM' AND name = 'Attacking Midfield'
        OR id = 'CF' AND name = 'Centre-Forward'
        OR id = 'SS' AND name = 'Second Striker'
        OR id = 'LW' AND name = 'Left Winger'
    )
);

CREATE TABLE IF NOT EXISTS "players" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "firstName" TEXT NOT NULL,
    "middleName" TEXT,
    "lastName" TEXT NOT NULL,
    "dateOfBirth" TEXT,
    "squadNumber" INTEGER NOT NULL UNIQUE,
    "positionId" TEXT NOT NULL,
    "teamId" TEXT NOT NULL,
    "starting11" BOOLEAN,
    FOREIGN KEY ("positionId") REFERENCES "positions"("id") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY ("teamId") REFERENCES "teams"("id") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE INDEX IF NOT EXISTS "idx_teams_leagueId" ON "teams"("leagueId");
CREATE INDEX IF NOT EXISTS "idx_players_positionId" ON "players"("positionId");
CREATE INDEX IF NOT EXISTS "idx_players_teamId" ON "players"("teamId");

Acceptance Criteria

• Create new tables for Position, Team, and League.
• Modify the Player table to use foreign keys referencing the new tables.
• Migrate existing data to match the new schema.
• Write integration tests to verify proper data relationships.

[coderabbitai]

🤖 CodeRabbit Planning

Planning is in progress. This will take a moment...

[coderabbitai]

Coding Plan

Approach

Ticket Summary

Normalize the Player database model by extracting Position, Team, and League into separate relational tables. This refactoring will change the Player primary key from Guid to INTEGER AUTOINCREMENT, establish foreign key relationships, and maintain the existing API contracts while improving data integrity and reducing redundancy.

Observations

Codebase Summary

This is an ASP.NET Core WebApi project using Entity Framework Core 9.0.11 with SQLite. The architecture follows a layered pattern with Repository (PlayerRepository extending Repository), Service (PlayerService with caching), and Controller layers. AutoMapper handles entity-to-DTO transformations, FluentValidation validates request models, and EF Core migrations manage database schema. The current Position implementation uses a smart enum pattern, while Team and League are stored as strings. The test infrastructure uses xUnit with Moq for unit tests, and DatabaseFakes exists for SQLite in-memory integration testing.

Assumptions

Assumption 1: Player ID breaking change

Options Considered:

• Preserve Guids by converting to integers via hash/mapping (complex, error-prone, non-standard)
• Accept breaking change and re-assign sequential integer IDs (clean slate, aligns with ticket DDL)

Chosen Option: Accept breaking change and re-assign sequential integer IDs (clean slate, aligns with ticket DDL)

Rationale: Accept the breaking change and re-assign sequential integer IDs during migration. This aligns with the provided DDL specification and is the standard approach for primary key type changes. The migration will drop and recreate the Players table with new integer IDs, re-seeding all player data from the existing seed data sources.

Assumption 2: Reuters team codes source

Options Considered:

• Use abbreviated team names as IDs (e.g., "MCI" for Manchester City)
• Create custom codes following Reuters-style conventions (3-letter abbreviations)
• Leave team.id as sequential integers instead of text codes

Chosen Option: Create custom codes following Reuters-style conventions (3-letter abbreviations)

Rationale: Create custom Reuters-style 3-letter codes for all 22 teams following common sports abbreviation conventions. This maintains the spirit of the DDL specification while being pragmatic about the lack of official Reuters codes. Document the mapping clearly in the migration code and seed data.

Assumption 3: Position smart enum handling

Options Considered:

• Keep Position enum alongside Position entity (dual representation, potentially confusing)
• Replace Position enum entirely with database entity and in-memory caching
• Keep Position enum for validation, entity for storage only

Chosen Option: Replace Position enum entirely with database entity and in-memory caching

Rationale: Replace the Position smart enum entirely with the Position entity table. Move validation logic to rely on the database CHECK constraint and simple in-memory lookup of valid position abbreviations. This eliminates the dual representation pattern and treats Position consistently with Team and League as normalized database entities.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Database Schema & Entity Models

Create new normalized entity models and EF Core migrations to transform the database schema. This phase establishes the new table structure, foreign key relationships, and migrates existing player data to the normalized schema.

Task 1: Create Position, Team, and League Entity Models

Define the three new entity classes in the Models directory.

• Create src/Dotnet.Samples.AspNetCore.WebApi/Models/Position.cs entity with Id (string, PK), Name (string) properties and EF Core configuration via data annotations
• Create src/Dotnet.Samples.AspNetCore.WebApi/Models/Team.cs entity with Id (string, PK), Name (string), LeagueId (int, FK) properties and navigation property to League
• Create src/Dotnet.Samples.AspNetCore.WebApi/Models/League.cs entity with Id (int, PK with AUTOINCREMENT), Name (string), Country (string), Confederation (string, nullable) properties and navigation collection to Teams
• Configure each entity to map to the table names specified in the DDL ("positions", "teams", "leagues")

Task 2: Update Player Entity Model

Transform the Player entity to use foreign keys and change the primary key type.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Models/Player.cs to change Id property from Guid to int with AUTOINCREMENT configuration
• Remove the Position, AbbrPosition, Team, and League string properties
• Add PositionId (string, FK) and TeamId (string, FK) properties
• Add navigation properties: Position, Team (with Team.League accessible via navigation chain)
• Update EF Core Fluent API configuration to define foreign key relationships and configure cascading behavior per DDL specification

Task 3: Update PlayerDbContext Configuration

Configure the new entities in the database context and establish relationships.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Data/PlayerDbContext.cs to add DbSet properties for Position, Team, and League entities
• Configure entity relationships in OnModelCreating() using Fluent API: Position-to-Player (one-to-many), Team-to-Player (one-to-many), League-to-Team (one-to-many)
• Configure indexes per DDL: idx_teams_leagueId, idx_players_positionId, idx_players_teamId
• Configure the Position CHECK constraint for valid id-name pairs as specified in the DDL
• Configure cascade delete behavior: League deletes cascade to Teams, but Position and Team deletes do not cascade to Players (NO ACTION per DDL)

Task 4: Create Reference Data Mapping Utilities

Build mapping data for the migration to convert existing string values to normalized foreign key references.

• Create src/Dotnet.Samples.AspNetCore.WebApi/Utilities/ReferenceDataMappings.cs utility class containing static data structures
• Define confederation mappings: map each of the 9 leagues to their country and confederation (use findings from web search: Premier League→England/UEFA, La Liga→Spain/UEFA, Serie A→Italy/UEFA, Ligue 1→France/UEFA, Liga Portugal→Portugal/UEFA, Bundesliga→Germany/UEFA, Eredivisie→Netherlands/UEFA, Copa de la Liga→Argentina/CONMEBOL, MLS→USA-Canada/CONCACAF)
• Define Reuters-style team code mappings: create 3-letter codes for all 22 unique teams from the seed data (e.g., "MCI" for Manchester City FC, "AVL" for Aston Villa FC, "INT" for Inter Milan, "BEN" for SL Benfica, etc.)
• Map each team to its league to establish team-league relationships
• Include source data comments referencing the PlayerData.cs seed file to maintain traceability

Task 5: Create EF Core Migration for Schema Transformation

Generate and customize a multi-step EF Core migration to handle the breaking schema change.

• Generate initial migration scaffolding using dotnet ef migrations add NormalizePlayerSchema
• Customize the generated migration's Up() method in src/Dotnet.Samples.AspNetCore.WebApi/Migrations/ to execute in this order:
  1. Create and seed the Positions table with 11 position records using migrationBuilder.InsertData() with data from the DDL CHECK constraint
  2. Create and seed the Leagues table with 9 league records using country and confederation data from ReferenceDataMappings
  3. Create and seed the Teams table with 22 team records using Reuters-style codes and team-to-league mappings from ReferenceDataMappings
  4. Drop the existing Players table entirely (breaking change due to PK type change from Guid to int)
  5. Create the new Players table with INTEGER AUTOINCREMENT PK, positionId and teamId FKs, and indexes
  6. Re-seed player data using modified PlayerData utility methods that generate players with integer IDs and FK references
• Implement Down() method to reverse the migration: drop new tables, recreate Players table with original Guid PK schema, re-seed original data
• Ensure foreign key constraints are created after reference data is seeded to avoid constraint violations

Task 6: Update PlayerData Seed Utility

Modify the existing PlayerData utility to work with the normalized schema and integer IDs.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Utilities/PlayerData.cs methods MakeStarting11WithId() and GetSubstitutesWithId()
• Replace Guid.Parse() calls with sequential integer ID generation (starting from 1)
• Remove Position, AbbrPosition, Team, League string assignments
• Add PositionId assignments using position abbreviations (e.g., "GK", "CB") that reference the Positions table
• Add TeamId assignments using Reuters-style team codes that reference the Teams table
• Ensure the same 26 players are seeded with equivalent data but using the new normalized references

🤖 Prompt for AI agents

This phase transforms the database schema from denormalized string fields to a
normalized relational structure with separate Position, Team, and League tables.

Create three new entity models:
- Position.cs with Id (string PK), Name properties
- Team.cs with Id (string PK), Name, LeagueId (FK), and League navigation
property
- League.cs with Id (int AUTOINCREMENT PK), Name, Country, Confederation
properties, and Teams collection

Update Player.cs entity:
- Change Id from Guid to int with AUTOINCREMENT
- Remove Position, AbbrPosition, Team, League string properties
- Add PositionId and TeamId foreign key properties
- Add Position and Team navigation properties

Update PlayerDbContext.cs:
- Add DbSet properties for Position, Team, League
- Configure one-to-many relationships: Position→Player, Team→Player, League→Team
- Add indexes: idx_teams_leagueId, idx_players_positionId, idx_players_teamId
- Configure Position CHECK constraint for valid id-name pairs
- Set cascade behavior: League→Team cascade, Position/Team→Player NO ACTION

Create ReferenceDataMappings.cs utility:
- Define confederation mappings for 9 leagues (Premier League→England/UEFA, La
Liga→Spain/UEFA, etc.)
- Define 3-letter Reuters-style team codes for all 22 teams (MCI, AVL, INT, BEN,
etc.)
- Map teams to leagues

Generate and customize EF Core migration:
- Create Positions table and seed 11 position records
- Create Leagues table and seed 9 league records with country/confederation
- Create Teams table and seed 22 team records with Reuters codes
- Drop existing Players table
- Create new Players table with int PK, positionId/teamId FKs, and indexes
- Re-seed 26 players with new integer IDs and FK references
- Implement Down() method to reverse all changes

Update PlayerData.cs utility:
- Replace Guid.Parse() with sequential integer ID generation
- Remove string property assignments for Position, Team, League
- Add PositionId assignments using abbreviations (GK, CB, etc.)
- Add TeamId assignments using Reuters team codes

Phase 2: Application Layer Updates

Update the service, repository, mapping, and validation layers to work with the normalized entity structure. This phase adapts the existing application code to use foreign key relationships and navigation properties while preserving the current API contracts.

Task 1: Update PlayerRepository for Navigation Properties

Extend the repository to eagerly load related entities when fetching players.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Repositories/PlayerRepository.cs GetAllAsync() and FindByIdAsync() methods to include .Include(p => p.Position).Include(p => p.Team).ThenInclude(t => t.League) for eager loading
• Add FindBySquadNumberAsync() to include the same navigation properties
• Ensure queries return players with fully populated Position, Team, and Team.League navigation properties to avoid lazy loading issues

Task 2: Update PlayerService for Entity Relationships

Modify service methods to work with navigation properties and maintain caching behavior.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Services/PlayerService.cs to rely on PlayerRepository's included navigation properties
• Ensure CreateAsync, UpdateAsync, and other methods receive PlayerRequestModel and correctly map to the Player entity with PositionId and TeamId
• Verify cache invalidation logic still works correctly with the new entity structure
• Update RetrieveAsync() and related methods to ensure related entities are available for AutoMapper transformation

Task 3: Remove Position Smart Enum

Delete the obsolete Position enum class since Position is now a database entity.

• Delete src/Dotnet.Samples.AspNetCore.WebApi/Enums/Position.cs file entirely
• Remove any using statements referencing the Position enum from other files
• Create a simple PositionLookup static utility if needed for in-memory validation of position abbreviations (can read from database once on startup and cache)

Task 4: Update AutoMapper Configuration

Reconfigure entity-to-DTO mappings to extract string values from navigation properties.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Mappings/PlayerMappingProfile.cs mapping from Player to PlayerResponseModel
• Map destination.Position from source.Position.Name (full position name from navigation property)
• Map destination.Club from source.Team.Name (team name from navigation property)
• Add new mapping for League: destination.League from source.Team.League.Name (league name via nested navigation)
• Update PlayerRequestModel to Player mapping: map source.AbbrPosition to destination.PositionId directly (string to string)
• Update PlayerRequestModel to Player mapping: determine destination.TeamId by looking up the team from the context or requiring it in the request model (may need to add TeamId to PlayerRequestModel or validate team existence separately)

Task 5: Update PlayerRequestModel and Validation

Adjust the request DTO and validation rules to work with normalized references.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Models/PlayerRequestModel.cs to potentially add TeamId property if team selection is required, or keep existing team name and lookup in validation
• Update src/Dotnet.Samples.AspNetCore.WebApi/Validators/PlayerRequestModelValidator.cs to replace Position.IsValidAbbr() validation
• Implement new validation rule that checks if AbbrPosition exists in the Positions table using a database query or in-memory cached lookup
• Add validation rule for TeamId (if added to request model) to ensure it exists in Teams table
• Consider whether team and position validation should query the database or use an in-memory cache of valid values

🤖 Prompt for AI agents

This phase adapts the application layer to work with the normalized database
schema while maintaining existing API contracts.

Update PlayerRepository.cs:
- Add eager loading to GetAllAsync() and FindByIdAsync() using .Include(p =>
p.Position).Include(p => p.Team).ThenInclude(t => t.League)
- Update FindBySquadNumberAsync() with same navigation property includes

Update PlayerService.cs:
- Ensure methods rely on repository's eager-loaded navigation properties
- Verify CreateAsync and UpdateAsync map PlayerRequestModel to Player with
PositionId and TeamId
- Confirm cache invalidation works with new entity structure
- Ensure RetrieveAsync provides related entities for AutoMapper

Remove Position smart enum:
- Delete Enums/Position.cs file
- Remove Position enum using statements from all files
- Create optional PositionLookup static utility for in-memory validation caching

Update PlayerMappingProfile.cs:
- Map Player → PlayerResponseModel: destination.Position from
source.Position.Name
- Map destination.Club from source.Team.Name
- Map destination.League from source.Team.League.Name
- Map PlayerRequestModel → Player: source.AbbrPosition to destination.PositionId
- Determine TeamId mapping strategy (lookup or add to request model)

Update validation:
- Modify PlayerRequestModel.cs to add TeamId if needed
- Update PlayerRequestModelValidator.cs to replace Position.IsValidAbbr() with
database/cache lookup
- Add validation for AbbrPosition exists in Positions table
- Add validation for TeamId exists in Teams table
- Implement validation strategy using database queries or in-memory cache

Phase 3: Integration Testing

Create integration tests using the DatabaseFakes infrastructure to verify foreign key relationships, data integrity constraints, and CRUD operations with the normalized schema.

Task 1: Set Up Integration Test Infrastructure

Prepare the test project to support integration tests with in-memory SQLite database.

• Update test/Dotnet.Samples.AspNetCore.WebApi.Tests/DatabaseFakes.cs CreateSqliteConnection() method if needed to ensure it supports the new normalized schema
• Create a test database seeding utility that populates Position, Team, and League reference data before running tests
• Ensure test database applies all EF Core migrations to establish the full normalized schema
• Create helper methods to quickly seed test players with valid foreign key references

Task 2: Test Foreign Key Relationships and Navigation Properties

Verify that entity relationships work correctly in the database.

• Create integration test class test/Dotnet.Samples.AspNetCore.WebApi.Tests/Integration/PlayerRelationshipsTests.cs
• Test that Player entities can be queried with Include() and navigation properties are populated (Player.Position, Player.Team, Player.Team.League)
• Test that attempting to create a Player with invalid PositionId or TeamId fails with foreign key constraint violation
• Test that Players are correctly associated with their Position and Team entities via foreign keys
• Test eager loading performance to ensure no N+1 query issues

Task 3: Test Data Integrity Constraints

Verify that database constraints enforce data integrity rules.

• Test that Position CHECK constraint prevents invalid position id/name combinations
• Test that League names must be unique (attempt to insert duplicate league name should fail)
• Test that Team names must be unique (attempt to insert duplicate team name should fail)
• Test that SquadNumber remains unique across all Players
• Test cascade delete behavior: deleting a League should cascade to Teams, deleting a Team should not cascade to Players (NO ACTION)

Task 4: Test Player CRUD with Normalized Schema

Verify that existing Player CRUD operations work correctly with the normalized database structure.

• Create integration test class test/Dotnet.Samples.AspNetCore.WebApi.Tests/Integration/PlayerCrudTests.cs
• Test creating a new Player with valid PositionId and TeamId references
• Test retrieving all Players returns correct Position, Team, and League information via navigation properties
• Test updating a Player's PositionId or TeamId successfully changes the relationships
• Test deleting a Player does not affect Position or Team entities
• Test that PlayerService methods (CreateAsync, RetrieveAsync, UpdateAsync, DeleteAsync) work correctly with the normalized schema

🤖 Prompt for AI agents

This phase creates comprehensive integration tests to verify the normalized
schema functions correctly.

Set up test infrastructure:
- Update DatabaseFakes.cs CreateSqliteConnection() for normalized schema support
- Create test seeding utility to populate Position, Team, League reference data
- Ensure test database applies all EF Core migrations
- Create helper methods to seed test players with valid FK references

Create PlayerRelationshipsTests.cs:
- Test Player queries with Include() populate navigation properties (Position,
Team, Team.League)
- Test creating Player with invalid PositionId/TeamId throws FK constraint
violation
- Test Players correctly associate with Position and Team via foreign keys
- Test eager loading has no N+1 query issues

Test data integrity constraints:
- Test Position CHECK constraint rejects invalid id/name combinations
- Test duplicate League names are rejected
- Test duplicate Team names are rejected
- Test SquadNumber uniqueness across all Players
- Test cascade delete: League→Team cascades, Team→Player does not (NO ACTION)

Create PlayerCrudTests.cs:
- Test creating Player with valid PositionId and TeamId
- Test retrieving all Players returns correct Position, Team, League via
navigation
- Test updating Player's PositionId or TeamId changes relationships
- Test deleting Player does not affect Position or Team entities
- Test PlayerService methods (CreateAsync, RetrieveAsync, UpdateAsync,
DeleteAsync) work with normalized schema

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

This phase transforms the database schema from denormalized string fields to a
normalized relational structure with separate Position, Team, and League tables.

Create three new entity models:
- Position.cs with Id (string PK), Name properties
- Team.cs with Id (string PK), Name, LeagueId (FK), and League navigation
property
- League.cs with Id (int AUTOINCREMENT PK), Name, Country, Confederation
properties, and Teams collection

Update Player.cs entity:
- Change Id from Guid to int with AUTOINCREMENT
- Remove Position, AbbrPosition, Team, League string properties
- Add PositionId and TeamId foreign key properties
- Add Position and Team navigation properties

Update PlayerDbContext.cs:
- Add DbSet properties for Position, Team, League
- Configure one-to-many relationships: Position→Player, Team→Player, League→Team
- Add indexes: idx_teams_leagueId, idx_players_positionId, idx_players_teamId
- Configure Position CHECK constraint for valid id-name pairs
- Set cascade behavior: League→Team cascade, Position/Team→Player NO ACTION

Create ReferenceDataMappings.cs utility:
- Define confederation mappings for 9 leagues (Premier League→England/UEFA, La
Liga→Spain/UEFA, etc.)
- Define 3-letter Reuters-style team codes for all 22 teams (MCI, AVL, INT, BEN,
etc.)
- Map teams to leagues

Generate and customize EF Core migration:
- Create Positions table and seed 11 position records
- Create Leagues table and seed 9 league records with country/confederation
- Create Teams table and seed 22 team records with Reuters codes
- Drop existing Players table
- Create new Players table with int PK, positionId/teamId FKs, and indexes
- Re-seed 26 players with new integer IDs and FK references
- Implement Down() method to reverse all changes

Update PlayerData.cs utility:
- Replace Guid.Parse() with sequential integer ID generation
- Remove string property assignments for Position, Team, League
- Add PositionId assignments using abbreviations (GK, CB, etc.)
- Add TeamId assignments using Reuters team codes
===============================================================================

Task: 2

This phase adapts the application layer to work with the normalized database
schema while maintaining existing API contracts.

Update PlayerRepository.cs:
- Add eager loading to GetAllAsync() and FindByIdAsync() using .Include(p =>
p.Position).Include(p => p.Team).ThenInclude(t => t.League)
- Update FindBySquadNumberAsync() with same navigation property includes

Update PlayerService.cs:
- Ensure methods rely on repository's eager-loaded navigation properties
- Verify CreateAsync and UpdateAsync map PlayerRequestModel to Player with
PositionId and TeamId
- Confirm cache invalidation works with new entity structure
- Ensure RetrieveAsync provides related entities for AutoMapper

Remove Position smart enum:
- Delete Enums/Position.cs file
- Remove Position enum using statements from all files
- Create optional PositionLookup static utility for in-memory validation caching

Update PlayerMappingProfile.cs:
- Map Player → PlayerResponseModel: destination.Position from
source.Position.Name
- Map destination.Club from source.Team.Name
- Map destination.League from source.Team.League.Name
- Map PlayerRequestModel → Player: source.AbbrPosition to destination.PositionId
- Determine TeamId mapping strategy (lookup or add to request model)

Update validation:
- Modify PlayerRequestModel.cs to add TeamId if needed
- Update PlayerRequestModelValidator.cs to replace Position.IsValidAbbr() with
database/cache lookup
- Add validation for AbbrPosition exists in Positions table
- Add validation for TeamId exists in Teams table
- Implement validation strategy using database queries or in-memory cache
===============================================================================

Task: 3

This phase creates comprehensive integration tests to verify the normalized
schema functions correctly.

Set up test infrastructure:
- Update DatabaseFakes.cs CreateSqliteConnection() for normalized schema support
- Create test seeding utility to populate Position, Team, League reference data
- Ensure test database applies all EF Core migrations
- Create helper methods to seed test players with valid FK references

Create PlayerRelationshipsTests.cs:
- Test Player queries with Include() populate navigation properties (Position,
Team, Team.League)
- Test creating Player with invalid PositionId/TeamId throws FK constraint
violation
- Test Players correctly associate with Position and Team via foreign keys
- Test eager loading has no N+1 query issues

Test data integrity constraints:
- Test Position CHECK constraint rejects invalid id/name combinations
- Test duplicate League names are rejected
- Test duplicate Team names are rejected
- Test SquadNumber uniqueness across all Players
- Test cascade delete: League→Team cascades, Team→Player does not (NO ACTION)

Create PlayerCrudTests.cs:
- Test creating Player with valid PositionId and TeamId
- Test retrieving all Players returns correct Position, Team, League via
navigation
- Test updating Player's PositionId or TeamId changes relationships
- Test deleting Player does not affect Position or Team entities
- Test PlayerService methods (CreateAsync, RetrieveAsync, UpdateAsync,
DeleteAsync) work with normalized schema

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- @coderabbitai You can skip phase 3. Add a simple unit test case for phase 2.
- @coderabbitai For assumption 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!