Breeding Table Schema

Last reviewed: 2026-09-07 — verified against live rs.breeding and qdb.breeding.

rs.breeding (local, DESKTOP-7UDSBA1) and qdb.breeding (ODIN) are column-identical: 23 columns, same order, types and nullability. id, sire_id and dam_id are shipped verbatim and never regenerated.

Columns

#	Column	Type	Null	Notes
1	id	int unsigned AI	no	Primary key. Referenced by sire_id/dam_id as self-referencing links, and by qdb.runner.breeding_id. Ships to QDB verbatim.
2	publish_name	varchar(45)	no	Display name, uppercased with apostrophes stripped. Part of the identity key.
3	sex	varchar(10)	yes	male or female only, derived from is_male. The source vocabulary (stallion / gelding / colt / mare / filly / rig) is deliberately not stored.
4	is_male	tinyint(1)	yes	1 male, 0 female. NULL blocks the row from loading (rule R3).
5	country_id	int unsigned	no	FK to country.id. Identical between rs.country and qdb.country across all 240 rows, so no remapping on ship. Part of the identity key.
6	year_of_foal	smallint unsigned	no	Part of the identity key.
7	foal_date	date	yes	Exact foaling date. NULL on every PedigreeQuery-sourced row — PQ publishes a year only. NULL is an accepted permanent state, not a gap to report against.
8	sire_name	varchar(45)	yes	Sire's display name as stated by the source.
9	sire_year	smallint unsigned	yes	Sire's year as stated — not corrected to the sire's own record, so a disagreement stays visible.
10	sire_country_id	int unsigned	yes	Sire's country as stated.
11	sire_id	int unsigned	yes	Resolved link to the sire's row. NULL means not yet resolvable, not absent.
12	dam_name	varchar(45)	yes	As sire_name.
13	dam_year	smallint unsigned	yes	As sire_year.
14	dam_country_id	int unsigned	yes	As sire_country_id.
15	dam_id	int unsigned	yes	As sire_id.
16	is_locked	tinyint(1)	no, dflt 0	When 1, importers and repair routines must not modify the row. The link pass skips locked rows, so locking before linking freezes NULL links permanently.
17	is_checked	tinyint(1)	no, dflt 0	Reviewed and verified against a trusted source. Automated loads leave it 0.
18	link_to_file	varchar(255)	yes	Provenance. A PQ page path, or horses_NNNN.jsonl.gz#hid=<id> for studbook rows. Records only the first source to claim the row.
19	notes	text	yes	Human free text. Exclude from SELECT *.
20	audit_notes	text	yes	Append-only log of manual changes. Exclude from SELECT *.
21	evidence_json	json	yes	Compact source evidence — summaries only, never raw payloads or full pedigree trees. breeding is the accepted truth; external sources are recorded here as evidence, not applied automatically.
22	mention_count	int unsigned	yes	How many source pages assert this identity. The one column exempt from never-overwrite: it accumulates. Max in the table: 960,446.
23	updated_at	datetime dflt CURRENT_TIMESTAMP	yes	datetime, not timestamp — this table outlives 2038. MySQL does not fire ON UPDATE when a statement changes no value, so an idempotent re-run leaves it alone.

Indexes

Index	Columns	Purpose
PRIMARY	id
uq_breeding_identity (unique)	publish_name, country_id, year_of_foal	The identity rule, enforced. Makes every parent join match at most one row, so ambiguity is impossible by construction.
idx_breeding_identity	publish_name, country_id, year_of_foal, sex, id	Who is this horse
idx_breeding_country_year	country_id, year_of_foal, publish_name, id	Crop lookups
idx_breeding_progeny_sire	sire_id, publish_name, year_of_foal, id	What did this horse produce
idx_breeding_progeny_dam	dam_id, publish_name, year_of_foal, id
idx_breeding_sire_match	sire_name, sire_country_id, sire_id	Find every unlinked child of a named parent
idx_breeding_dam_match	dam_name, dam_country_id, dam_id
idx_breeding_review	is_checked, is_locked, id	Review queue
idx_breeding_studbook_lock	publish_name, country_id, year_of_foal, sire_name, dam_name, is_locked	The studbook five-way match

Ten indexes, down from 19 index parts in the pre-v3 table. Dropped were single-column keys on sex, is_locked, is_checked, foal_date, year_of_foal, country_id, publish_name, sire_id and dam_id — every one either a prefix of a composite that remains or too low-cardinality to be chosen. Fewer indexes also means materially faster inserts across 3 M rows.

No foreign keys. The load inserts children before their parents exist. They are added after a build, where the validating ALTER doubles as an integrity proof.

Live population

Measure	Value
Rows	3,106,726
Sire named / linked	3,036,399 / 3,031,580
Dam named / linked	3,036,442 / 2,981,831
Both linked	2,977,656
Named sire not yet linked	4,819
Named dam not yet linked	54,611
No sire named at all	70,327
No dam named at all	70,284
foal_date present	776,711
mention_count present	2,496,495 (PQ-sourced rows)
is_locked / is_checked	0 / 0

Rows with no parent named are the foundation stock and the leaf edges of the scrape. Rows with a parent named but unlinked are the ranked scrape backlog — reports.py writes unlinked_sires.csv and unlinked_dams.csv, ordered by how many children are waiting behind each missing horse.

Walking a pedigree

Ancestry is a recursive walk, not stored columns. Two separate UNION ALL branches:

WITH RECURSIVE line AS (
    SELECT id, publish_name, country_id, year_of_foal, sire_id, dam_id,
           0 AS gen, CAST('' AS CHAR(200)) AS path
      FROM breeding
     WHERE publish_name = 'WINX' AND country_id = ? AND year_of_foal = 2011
    UNION ALL
    SELECT b.id, b.publish_name, b.country_id, b.year_of_foal, b.sire_id, b.dam_id,
           l.gen + 1, CONCAT(l.path, 'S')
      FROM line l JOIN breeding b ON b.id = l.sire_id
     WHERE l.gen < 30
    UNION ALL
    SELECT b.id, b.publish_name, b.country_id, b.year_of_foal, b.sire_id, b.dam_id,
           l.gen + 1, CONCAT(l.path, 'D')
      FROM line l JOIN breeding b ON b.id = l.dam_id
     WHERE l.gen < 30
)
SELECT gen, path, publish_name, year_of_foal, country_id FROM line ORDER BY gen, path;

Never collapse this to ON b.id IN (l.sire_id, l.dam_id). An IN list holding column expressions rather than constants cannot resolve to an index lookup, so MySQL scans the whole table for every driving row. Both branches above are eq_ref on the primary key, which is linear. This mistake cost the project a half-hour hang on a 503,100-row table.

The walk terminates naturally at foundation horses — the ones with sire_id and dam_id both NULL, which is what an unknown-parent foundation mare is.

Measured depth

A generation counts as complete only when every ancestor at that level is present.

	Horses	Share	Ancestors required
Generation 1	2,977,656	95.85 %	2
Generation 2	2,948,004	94.89 %	6
Generation 3	2,928,713	94.27 %	14
Generation 4	2,912,452	93.75 %	30
Generation 5	2,895,470	93.20 %	62

Schema history

Version	Date	Change
v3	17–21 Aug 2026	One canonical CREATE TABLE for both servers. Added sire_year, sire_country_id, dam_year, dam_country_id. Dropped new_id and guid_id. Column order made identical across servers. 19 index parts → 10 indexes. Every column commented.
v4 (additive)	24 Aug 2026	Added sire_key_name, dam_key_name, mention_count, updated_at.
v4 (current)	27 Aug 2026	Dropped key_name, sire_key_name, dam_key_name. Identity became (publish_name, country_id, year_of_foal), enforced by uq_breeding_identity. mention_count and updated_at retained. The loaders call require_new_schema() and refuse to run if the dropped columns are still present.

Canonical DDL: breeding_v3_LOCAL_rs.sql and breeding_v3_ODIN_qdb.sql, plus the 27 August ALTER. Note the v3 files still show key_name — they predate the drop.