army_units

Unit rows belonging to a specific army list

SQL Army System Transactional

army_units

Purpose

Stores the actual units inside an army list. These are user- or import-created unit rows and may either link to a canonical master unit in units or exist as legacy/free-text rows without a valid master unit link.

Role in the system

units (canonical)
   ↑
army_units
   ↑
army_lists

This table is where army construction becomes gameplay-ready. It holds points, flags, notes, quantity, and ordering for units that will later be used in games, previews, imports, and scoring flows.

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`army_list_id`	bigint unsigned	No	Parent army
`master_unit_id`	int	Yes	Original master unit reference, may be non-positive or null in legacy flows
`name`	varchar(200)	No	Unit display name stored on the army row
`unit_type`	varchar(100)	Yes	Hero/Core/etc on the army row
`points`	int	No	Points value stored on this army unit
`quantity`	int	No	Quantity count, default 1
`notes`	varchar(500)	Yes	Imported options / notes text
`is_general`	tinyint(1)	No	General flag
`is_bsb`	tinyint(1)	No	Battle standard bearer flag
`has_standard`	tinyint(1)	No	Unit has standard/banner flag
`sort_order`	int	No	Explicit UI/display ordering
`created_at`	datetime(3)	No	Creation timestamp
`updated_at`	datetime(3)	No	Last update timestamp
`master_unit_id_fk`	int unsigned (generated)	Yes	Computed foreign-key-safe version of `master_unit_id`

Generated column behaviour

master_unit_id_fk is generated as:

CASE WHEN master_unit_id > 0 THEN master_unit_id ELSE NULL END

This allows the schema to support legacy/non-canonical rows while still enforcing a foreign key for valid linked rows.

Keys and indexes

PRIMARY KEY (id)
ix_units_list (army_list_id)
ix_units_list_sort (army_list_id, sort_order, id)
idx_army_units_master_unit_id (master_unit_id)
idx_army_units_master_unit_id_fk (master_unit_id_fk)

Foreign keys

fk_units_list: army_units.army_list_id → army_lists.id ON DELETE CASCADE
fk_army_units_master_unit: army_units.master_unit_id_fk → units.id

Relationships

Relationship	Type	Notes
`army_units.army_list_id → army_lists.id`	Many-to-one	Each unit row belongs to one army
`army_units.master_unit_id_fk → units.id`	Optional many-to-one	Only applies when a valid positive master unit id exists
`game_units` derived from army_units	Copied/expanded downstream	Gameplay state later depends on these rows

Gameplay-relevant flags

is_general affects scoring bonuses
is_bsb affects scoring bonuses
has_standard affects banner scoring logic

Used by

Army units create/get/list/update/delete endpoints
Army import commit endpoints
TO entrant army unit preview
Game setup and game-unit generation flows
Scoring logic after game units are instantiated

Design notes

This table intentionally stores a snapshot-like unit row, not just a pure reference
Points are stored here per army unit, not on canonical units
The schema supports both strongly linked and weakly linked unit rows
sort_order is central to predictable UI rendering

Risks

Mixed canonical and legacy behaviour increases complexity
Free-text rows can reduce downstream consistency
Changing unit flags incorrectly can distort gameplay scoring
Large notes payloads from imports may affect readability and maintenance

Recommended future improvements

Decide whether legacy/unlinked units should remain supported long-term
Consider stricter validation around valid unit_type values
Document or enforce single-general / single-BSB rules if needed
Consider wider use of generated/FK-safe patterns if legacy support continues