tournament_entrant_armies

Linking table between tournament entrants and army lists

SQL Tournaments Linking Layer

tournament_entrant_armies

Purpose

Links tournament entrants to army lists. This table supports TO workflows where an entrant has a primary army associated with the tournament, regardless of whether the entrant is a registered user or a guest entrant.

Role in the system

tournament_entrants
 └── tournament_entrant_armies
      └── army_lists

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`entrant_id`	bigint unsigned	No	Parent entrant
`army_list_id`	bigint unsigned	No	Linked army
`is_primary`	tinyint(1)	No	Primary army flag
`created_at`	datetime(3)	No	Creation timestamp
`updated_at`	datetime(3)	No	Last update timestamp
`deleted_at`	datetime(3)	Yes	Soft delete marker

Keys and indexes

PRIMARY KEY (id)
UNIQUE KEY uq_tea_entrant_army (entrant_id, army_list_id)
UNIQUE KEY uq_tea_primary_active (entrant_id, is_primary, deleted_at)
KEY idx_tea_army (army_list_id)
KEY idx_tea_deleted_at (deleted_at)

Foreign keys

fk_tea_entrant: tournament_entrant_armies.entrant_id → tournament_entrants.id ON DELETE CASCADE
fk_tea_army: tournament_entrant_armies.army_list_id → army_lists.id ON DELETE CASCADE

Business rules expressed in schema

An entrant cannot be linked to the same army more than once
The table attempts to support one active primary army per entrant

The unique key uq_tea_primary_active (entrant_id, is_primary, deleted_at) is the key mechanism here. In practice, application logic still needs to be careful with how soft deletes and primary switching are performed.

Relationships

Relationship	Type	Notes
`tournament_entrant_armies.entrant_id → tournament_entrants.id`	Many-to-one	Army link belongs to one entrant
`tournament_entrant_armies.army_list_id → army_lists.id`	Many-to-one	Links to one army list

Used by

TO event entrant army get/link/unlink flows
Entrant army import workflows
TO display and preview of linked entrant armies

Design notes

This is a linking table with business meaning, not just a neutral many-to-many join
The is_primary flag is central to current TO workflow assumptions
Soft delete allows unlinking without physical removal
Army lists linked here are intentionally excluded from some normal user army selectors/listings

Risks

The unique strategy for primary rows depends on correct handling of deleted_at
If application logic bypasses the intended unlink/link sequence, primary integrity can become awkward
This table couples TO workflows directly to the general army system

Recommended future improvements

Document active-primary switching workflow explicitly
Consider a simpler dedicated primary-army invariant if entrant-army complexity grows
Add audit tracking if TO operational history becomes important