A Horse Racing Data Model: Entities, Keys and Joins

A good horse racing data model fits on one page. There are about eight entities, a handful of keys, and one rule about what a row means. Getting that straight before you write a single query saves you from the bugs that cost the most time later, like a join that doubles your row count or a feature that peeks at the future.

This is the model we use, taken from the tables behind our UK and Hong Kong data.

The entities

Hong Kong adds veterinary records, keyed by race, horse and date, and a dividends table keyed by race and betting pool.

The row that matters is the record. Its grain is one runner in one race, and the pair of race and horse is unique: a horse appears once in a race. If a join to records gives you more rows than you expected, something you joined on is not unique, and that is the bug.

A join that works

Here is a query we ran against the UK tables to list the winners at Kempton on 30 September 2026, with horse, jockey and trainer names resolved:

SELECT r.Date, c.course_name, r.race_time, r.Distance, rec.Place,
       h.name AS horse, j.Name AS jockey, t.Name AS trainer
FROM records rec
JOIN races r          ON r.Race_ID = rec.Race_ID
JOIN courses c        ON c.course_id = r.course_id
JOIN horses h         ON h.id = rec.Horse_ID
LEFT JOIN jockeys_stats j  ON j.Jockey_ID = rec.jockey_ID
LEFT JOIN trainers_stats t ON t.Trainer_ID = rec.trainer_ID
WHERE r.Date = '2026-09-30' AND c.course_name = 'Kempton' AND rec.Place = '1'
ORDER BY r.race_time;

A note on naming: in the SQL tables the horse’s own key column is called id, while the API calls it horseID. The first three rows come back as a 6f race at 16:25, a 7f race at 16:55 and a mile race at 17:30. Note the LEFT JOIN for jockeys and trainers: a runner can be missing one, and an inner join would silently drop it.

Racecards and records

A racecard row and a record row describe the same runner at two moments. The racecard exists before the race and holds what was declared: weight, draw, gear, jockey, rating. The record exists after and holds what happened: place, margin, times, starting price.

Keep the two apart in your model. Anything you use as a predictor should come from the racecard side, or from records of earlier races. Using a record field for the race you are predicting is leakage, and it is the easiest mistake in racing data to make. Our feature engineering guide shows how to build the earlier-races part safely.

Two traps

IDs belong to a market. UK and Hong Kong data live in separate schemas with their own keys. A horse ID of 83173 in the UK tables says nothing about a Hong Kong horse with the same number. If you load both into one warehouse, add a market column and make it part of every key.

Profile totals look into the future. Horse, jockey and trainer profile tables carry career totals: runs, wins, places, and splits by surface. They are snapshots, stamped with an uptodate date. If you join a profile onto a historical race, the totals include every run after that race. A horse that has won ten of its twenty runs looks like a winner in the first race you join it to, because the answer is in the totals.

Compute your own historical totals from the records table, as of each race date. Use the profile tables for display and for current-day lookups, not for training.

Names are not keys

Names repeat. In September 2026 two different horses called All Good were running, one by Equiano and one by Tasleet, and they have different IDs. Joins on name merge them into one career. Join on the ID, and treat the name as a label.

The same is true of courses, where hyphenation and spelling vary. Use course_id.

What this looks like in the data and API

Draw the model on one page before you load anything, then check it against a real race. The datasets come as CSV, SQL and JSON with these entities as separate files, and the API mirrors them for both markets. If you are still choosing a source, our dataset checklist covers the tests worth running first.