tournament_scoring_system_bands

Points-difference band rules for tournament scoring systems

SQL Tournaments Rules Engine

tournament_scoring_system_bands

Purpose

Stores the individual scoring bands for a tournament scoring system. Each row maps a points-difference range to winner/loser tournament points, or to draw points.

Role in the system

tournament_scoring_systems
 └── tournament_scoring_system_bands
      ↓
game result → band lookup → tournament points

Columns

Column	Type	Null	Notes
`id`	int unsigned	No	Primary key
`scoring_system_id`	int unsigned	No	Parent scoring system
`sort_order`	smallint unsigned	No	Display/evaluation order
`points_diff_min`	int unsigned	No	Inclusive lower bound
`points_diff_max`	int unsigned	Yes	Inclusive upper bound, null may imply open-ended
`is_draw_band`	tinyint(1)	No	Draw rule flag
`winner_points`	tinyint unsigned	Yes	Tournament points for winner
`loser_points`	tinyint unsigned	Yes	Tournament points for loser
`draw_points`	tinyint unsigned	Yes	Tournament points for each side in draw band
`created_at`	datetime	No	Creation timestamp
`updated_at`	datetime	No	Last update timestamp

Keys and indexes

PRIMARY KEY (id)
KEY idx_tssb_system_sort (scoring_system_id, sort_order)
KEY idx_tssb_system_range (scoring_system_id, points_diff_min, points_diff_max)

Foreign keys

fk_tssb_scoring_system: tournament_scoring_system_bands.scoring_system_id → tournament_scoring_systems.id ON DELETE CASCADE ON UPDATE CASCADE

Band model

Each scoring system contains multiple ordered bands. A game’s final points difference is matched to one band, which then yields tournament points.

Normal band: uses winner_points and loser_points
Draw band: uses draw_points when is_draw_band = 1

Used by

Tournament standings calculation
Round and performance summaries
TO scoring-system management

Design notes

This is effectively the rules-engine data table for tournament points
Ordering matters, which is why sort_order is stored explicitly
Range overlap prevention is not DB-enforced; application logic must keep bands consistent

Risks

Overlapping or gapped bands can produce incorrect tournament-point assignment
Changing bands in place can affect historical standings if not versioned

Recommended future improvements

Add validation tooling for overlap/gap detection
Version scoring bands for live or completed tournaments
Document inclusive/exclusive range semantics explicitly in application logic