game_turns
Game-wide turn sequence table
SQL
Gameplay
Turn Model
game_turns
Purpose
Stores the high-level turn sequence for a game. This table represents each full game turn and provides the container for per-side/player turns.
Columns
| Column | Type | Null | Notes |
|---|---|---|---|
id | bigint unsigned | No | Primary key |
game_id | bigint unsigned | No | Parent game |
turn_number | int | No | Sequential game-turn number |
started_at | datetime(3) | No | Turn start |
ended_at | datetime(3) | Yes | Turn end |
created_at | datetime(3) | No | Creation timestamp |
updated_at | datetime(3) | No | Last update timestamp |
Keys and indexes
PRIMARY KEY (id)UNIQUE KEY uq_game_turn (game_id, turn_number)KEY ix_turns_game (game_id)
Foreign keys
fk_turns_game: game_turns.game_id → games.id ON DELETE CASCADE
Relationships
| Relationship | Type | Notes |
|---|---|---|
game_turns.game_id → games.id | Many-to-one | Turn belongs to one game |
game_player_turns.turn_id → game_turns.id | One-to-many (logical) | Player turns sit inside a game turn |
game_events.turn_id → game_turns.id | One-to-many | Events may attach to a game turn |
Used by
- Turns/start and turns/end flows
- Event creation when an active turn is required
- Game play-state derivation
Design notes
- Uniqueness of
(game_id, turn_number)ensures no duplicate turn numbers per game - Open/active turn is represented by
ended_at IS NULL - This table is intentionally lightweight; detailed side order lives in player turns
Risks
- No DB-level enforcement that only one active turn exists at a time per game
- Consistency depends on application logic correctly ending one turn before starting another
Recommended future improvements
- Consider documenting single-active-turn invariant explicitly
- Add partial/index strategy if active-turn lookups become heavy