tournament_entrants

Entrant records for tournament participation

SQL Tournaments Operational

tournament_entrants

Purpose

Stores all entrants for a tournament, including both registered users and non-registered/guest entrants. This table is the centre of TO entrant management, claim-code workflows, and event-linked game mapping.

Role in the system

tournaments
 └── tournament_entrants
      ├── tournament_entrant_armies
      └── games (entrant A/B references)

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`tournament_id`	int unsigned	No	Parent tournament
`user_id`	bigint unsigned	Yes	Registered user if entrant is linked
`display_name`	varchar(120)	No	Entrant display name
`email`	varchar(255)	Yes	Optional email, mainly for claim/send flows
`notes`	varchar(500)	Yes	Optional TO notes
`is_registered`	tinyint(1)	No	Registered vs guest entrant flag
`created_at`	datetime(3)	No	Creation timestamp
`updated_at`	datetime(3)	No	Last update timestamp
`deleted_at`	datetime(3)	Yes	Soft delete marker
`claimed_by_user_id`	bigint unsigned	Yes	User who completed claim process
`claimed_at`	datetime(3)	Yes	Claim completion timestamp

Keys and indexes

PRIMARY KEY (id)
KEY idx_te_tournament (tournament_id)
KEY idx_te_user (user_id)
KEY idx_te_deleted_at (deleted_at)
KEY idx_te_claimed_by_user (claimed_by_user_id)
UNIQUE KEY uq_tournament_registered_user (tournament_id, user_id)

Foreign keys

fk_te_tournament: tournament_entrants.tournament_id → tournaments.id ON DELETE CASCADE
fk_te_user: tournament_entrants.user_id → users.id ON DELETE SET NULL
fk_te_claimed_by: tournament_entrants.claimed_by_user_id → users.id ON DELETE SET NULL

Entrant models supported

Entrant type	Typical shape	Notes
Registered entrant	`is_registered = 1`, `user_id` set	Directly linked to existing user
Guest entrant	`is_registered = 0`, `user_id` null	May later be claimed/linked
Claimed guest entrant	Guest row plus claim metadata	Tracks conversion flow

Relationships

Relationship	Type	Notes
`tournament_entrant_armies.entrant_id → tournament_entrants.id`	One-to-many	Army linking layer
`games.tournament_entrant_a_id / b_id → tournament_entrants.id`	Referenced externally	Event game player mapping

Used by

TO event entrants list/save/get/delete flows
Claim-code creation and email sending flows
Entrant army linking/import flows
Tournament-linked game setup

Design notes

This table intentionally supports mixed entrant types in one structure
The unique key on (tournament_id, user_id) protects against duplicate registered entrants
Guest uniqueness is not protected by the same key because user_id may be null
Soft delete is supported, so list and uniqueness logic must account for active rows only where appropriate

Risks

Guest entrant duplication must be handled in application logic
Claim flows add lifecycle complexity to what would otherwise be a simple join table
Email and identity linkage can drift if claim/update logic is inconsistent

Recommended future improvements

Document guest de-duplication rules explicitly
Consider adding stronger uniqueness rules for non-registered entrants if business rules settle
Add clearer auditability around claim transitions