tournaments

Tournament configuration and identity table

SQL Tournaments Configuration

tournaments

Purpose

Stores tournament records used to group games into a structured competition context. This table is the root of the tournament domain and provides the linkage used by standings, rounds, entrant management, and tournament-specific scoring.

Role in the system

tournaments
 ├── games
 └── tournament_entrants
      └── tournament_entrant_armies

Columns

Column	Type	Null	Notes
`id`	int unsigned	No	Primary key
`user_id`	bigint unsigned	No	Owning/creating user
`name`	varchar(200)	No	Tournament name
`location`	varchar(200)	Yes	Optional location text
`organiser`	varchar(200)	Yes	Optional organiser label
`event_date`	date	Yes	Optional event date
`game_system`	varchar(100)	Yes	System label, typically The Old World
`status`	varchar(20)	No	Open/closed style lifecycle text
`is_default`	tinyint(1)	No	Template/default flag
`scoring_system_id`	int unsigned	Yes	Optional scoring system reference
`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)
KEY idx_tournaments_user (user_id)
KEY idx_tournaments_status (status)
KEY idx_tournaments_deleted_at (deleted_at)
KEY idx_tournaments_scoring_system (scoring_system_id)

Foreign keys

fk_tournaments_user: tournaments.user_id → users.id ON DELETE CASCADE
fk_tournaments_scoring_system: tournaments.scoring_system_id → scoring_systems.id ON DELETE SET NULL ON UPDATE CASCADE

Relationships

Relationship	Type	Notes
`games.tournament_id → tournaments.id`	One-to-many	Games may belong to a tournament
`tournament_entrants.tournament_id → tournaments.id`	One-to-many	Entrants are scoped to a tournament

Used by

TO events list and event edit/save flows
Game setup when selecting tournament context
Tournament standings and round info queries
Admin tournament read/write utilities

Design notes

This is both a tournament record and the TO event record in practical terms
Soft delete is supported, so list queries must consistently exclude deleted rows
Scoring logic is linked via scoring_system_id, not duplicated here
Status is application-controlled free text rather than DB enum-constrained

Risks

Changing tournament configuration after games exist can alter standings semantics
Status values are not DB-constrained, so consistency depends on application logic
Soft delete requires careful filtering across TO and stats flows

Recommended future improvements

Document locking rules once games/rounds exist
Consider constraining status values more strongly
Consider versioning configuration changes for live tournaments