# Kinderkopje data: tables and fields

Rankings, calendars and results come from the Union Cycliste Internationale (UCI): uci.org and DataRide. Team levels come from Wikipedia (CC BY-SA 4.0).

## team_snapshot

One row per captured UCI World Ranking (team ranking) date.

|Field|Meaning|
|:-|:-|
|`id`|Row number, used by team_rank.|
|`season`|The season the ranking belongs to. UCI points start again at zero on 1 January.|
|`date`|The ranking date shown on DataRide, as yyyy-mm-dd. Unique.|
|`moment_id`|UCI's own number for this ranking snapshot.|
|`complete`|1 if every team of the ranking was captured, 0 if only some pages were (the usual case: top 40).|

Links: team_rank.snapshot_id = team_snapshot.id

Example, which ranking dates have been captured?:

```sql
SELECT season, date, complete, (SELECT COUNT(*) FROM team_rank r WHERE r.snapshot_id = s.id) AS teams FROM team_snapshot s ORDER BY date
```

## team_rank

The teams of each captured ranking: position and points on that date.

|Field|Meaning|
|:-|:-|
|`snapshot_id`|Which ranking date: team_snapshot.id.|
|`team_id`|UCI's team number. It stays with the team's licence when the name or code changes (UAE: UAD in 2025, UEX in 2026), so use it to follow a team over the years.|
|`code`|The team's UCI code in that season, for example UAD.|
|`name`|The team's name in that season.|
|`nation`|The team's nation (UCI's three-letter code).|
|`rank`|Position in the ranking on that date.|
|`points`|UCI World Ranking points, season to date.|

Links: snapshot_id -> team_snapshot.id; team_id also appears in team_level

Example, the top 10 of the newest captured ranking:

```sql
SELECT rank, code, name, points FROM v_team_points WHERE date = (SELECT MAX(date) FROM team_snapshot) ORDER BY rank LIMIT 10
```

## team_level

Which teams were WorldTeams or ProTeams in a season (from Wikipedia). Teams that are not listed are lower-level, mostly Continental.

|Field|Meaning|
|:-|:-|
|`season`|The season.|
|`team_id`|UCI's team number, as in team_rank.|
|`level`|WT = WorldTeam, PT = ProTeam.|

Links: team_id = team_rank.team_id

Example, worldteams and proteams per season:

```sql
SELECT season, level, COUNT(*) AS teams FROM team_level GROUP BY season, level ORDER BY season DESC, level
```

## race_result

One row per captured race edition (a race in one year). Each edition has its own number on DataRide, UCI's results site.

|Field|Meaning|
|:-|:-|
|`uci_id`|DataRide's competition number for this edition (the number in dataride.uci.ch/iframe/CompetitionResults/<number>). Each year of a race has its own.|
|`year`|The year of the edition.|
|`name`|The race name as DataRide writes it.|
|`class`|The UCI class, for example 2.UWT, 1.Pro, 1.1.|
|`class_text`|The full class text from the page, for example '1.Pro - 1 day - UCI ProSeries'.|
|`dates`|The dates as UCI writes them, for example '11 Oct 2026'.|
|`country`|The country (UCI's three-letter code).|
|`captured_at`|When it was captured (date and time).|
|`contributor`|Who captured it. Not used by the current capture; always empty in a public copy.|
|`race_id`|Which race this edition belongs to: race.race_id. The same race has the same id in every year, whatever it was called.|

Links: classification.uci_id = race_result.uci_id

Example, races captured so far, and by whom:

```sql
SELECT year, name, class, dates, contributor FROM race_result ORDER BY year, name
```

## classification

A result list of a race: the General Classification or one stage (one DataRide event).

|Field|Meaning|
|:-|:-|
|`id`|Row number, used by result_row.|
|`uci_id`|Which race edition: race_result.uci_id.|
|`label`|The name of the event, for example 'General Classification' or 'Stage 3'.|
|`title`|The page's heading, for example 'Giro del Veneto Individual Road Race'.|
|`total_rows`|How many rows the table had. Only the rows that score points are kept in result_row.|
|`event_id`|DataRide's number for this event (the number in .../EventResults/<number>).|

Links: uci_id -> race_result.uci_id; result_row.classification_id = classification.id

Example, how many rows scored points, out of how many:

```sql
SELECT r.name, r.year, c.label, c.total_rows, (SELECT COUNT(*) FROM result_row w WHERE w.classification_id = c.id) AS scoring_rows FROM classification c JOIN race_result r ON r.uci_id = c.uci_id ORDER BY r.year, r.name
```

## result_row

One row per rider who scored UCI points in a result list.

|Field|Meaning|
|:-|:-|
|`classification_id`|Which result list: classification.id.|
|`rank`|The place in that list. Empty when the page showed none.|
|`rider`|First name and SURNAME as UCI writes them.|
|`team`|The team name as written on the race page. This is a name, not a team number.|
|`age`|The rider's age at the time of the race, as DataRide shows it (19 to 50 in the captured data, present on every row). Part of the public file.|
|`nation`|The rider's nation (three-letter code).|
|`result`|The time or gap as shown, for example 4:31:46 or +02.|
|`points`|UCI points this result gave.|

Links: classification_id -> classification.id. The view v_results joins all three tables.

Example, the riders who scored most in the captured races:

```sql
SELECT rider, team, SUM(points) AS points, COUNT(*) AS results FROM v_results GROUP BY rider, team ORDER BY points DESC LIMIT 20
```

## race

One row per race (men's or women's elite), the same race in every year whatever it was called. Races that changed their name are joined into one.

|Field|Meaning|
|:-|:-|
|`race_id`|The race's number.|
|`key`|What the number follows when races are joined: a short form of the core name, with the category.|
|`name`|The internal name: what this project calls the race. By default the shortest name UCI has used, without sponsors; it can be renamed.|
|`slug`|The internal name as a short, web-safe text, for example classic-brugge-de-panne.|
|`category`|ME = men's elite, WE = women's elite.|
|`first_year`|The first year in the data.|
|`last_year`|The last year in the data.|

Links: race_name.race_id = race.race_id; race_result.race_id = race.race_id

Example, races and the years they cover:

```sql
SELECT name, category, first_year, last_year FROM race ORDER BY name
```

## race_name

The external names of each race: how a source wrote its name, with the years it was used. A race that changed sponsor has several.

|Field|Meaning|
|:-|:-|
|`race_id`|Which race: race.race_id.|
|`name`|The name as the source wrote it, for example 'MINERVA CLASSIC BRUGGE - DE PANNE'.|
|`source`|Where the name comes from: uci for DataRide.|
|`first_year`|The first year this name was used.|
|`last_year`|The last year this name was used.|

Links: race_id -> race.race_id

Example, every name each race has had:

```sql
SELECT r.name AS race, n.name AS external_name, n.first_year, n.last_year FROM race_name n JOIN race r ON r.race_id = n.race_id ORDER BY r.name, n.first_year
```

## race_link

Where each race lives on the web: official site, Wikipedia, social media and FirstCycling.

|Field|Meaning|
|:-|:-|
|`race_id`|Which race: race.race_id.|
|`kind`|website, wikipedia, wikidata, x, instagram, facebook, youtube, tiktok, firstcycling or other.|
|`label`|The name to show for the link, for example Instagram.|
|`url`|The address.|
|`source`|Where it came from: registry (the race registry of the Threads tab), wikidata (a match you checked) or manual (typed in).|

Links: race_id -> race.race_id

Example, the links of every race that has any:

```sql
SELECT r.name, l.label, l.url FROM race_link l JOIN race r ON r.race_id = l.race_id ORDER BY r.name, l.kind
```

## country

Every country code UCI uses, with its continent and UCI confederation, for filtering races, riders and teams by continent.

|Field|Meaning|
|:-|:-|
|`code`|The code UCI uses (IOC style): ITA, GER, SUI ... It is the value in queue_race.country, result_row.nation and team_rank.nation.|
|`name`|The country's name.|
|`continent`|Europe, Asia, Africa, North America (with Central America and the Caribbean), South America or Oceania.|
|`confederation`|UCI's continental confederation: Europe, Asia, Africa, Pan America or Oceania.|

Links: country.code = queue_race.country, result_row.nation, team_rank.nation

Example, races captured per continent:

```sql
SELECT c.continent, COUNT(*) AS races FROM queue_race q JOIN country c ON c.code = q.country GROUP BY c.continent ORDER BY races DESC
```

## queue_race

The competitions to capture, taken from the DataRide competition list pages you captured.

|Field|Meaning|
|:-|:-|
|`uci_id`|DataRide's competition number for this edition.|
|`year`|The year.|
|`name`|The race name.|
|`start`|First day, yyyy-mm-dd.|
|`end`|Last day, yyyy-mm-dd (the same for a one-day race).|
|`country`|The country (UCI's three-letter code).|
|`hub`|Where the line came from (DataRide).|
|`class`|The UCI class from the list, for example 2.UWT, 1.Pro, 1.1.|
|`category`|ME = men, WE = women, from the list's Category filter or the race name.|
|`race_id`|Which race this edition belongs to: race.race_id.|

Links: uci_id = race_result.uci_id once the race has been captured; queue_event.uci_id = queue_race.uci_id

Example, races in the queue that are not captured yet:

```sql
SELECT q.year, q.name, q.start, q.hub FROM queue_race q WHERE q.uci_id NOT IN (SELECT uci_id FROM race_result) ORDER BY q.start
```

## queue_event

The events of a queued competition (General Classification, each stage), read from its DataRide competition page.

|Field|Meaning|
|:-|:-|
|`uci_id`|Which competition: queue_race.uci_id.|
|`event_id`|DataRide's number for the event.|
|`name`|The event's name, for example 'Stage 3'.|

Links: uci_id -> queue_race.uci_id; event_id = classification.event_id once captured

Example, events still to capture:

```sql
SELECT q.year, q.name, e.name AS event FROM queue_event e JOIN queue_race q ON q.uci_id = e.uci_id WHERE e.event_id NOT IN (SELECT event_id FROM classification WHERE event_id IS NOT NULL) ORDER BY q.start, e.event_id
```

## kv

Texts and settings you edit on the pages, stored with the data.

|Field|Meaning|
|:-|:-|
|`key`|The name: season (YAML), registry (YAML), layout (JSON), sharenote and a few more.|
|`value`|The text.|

Example, what is saved:

```sql
SELECT key, LENGTH(value) AS characters FROM kv ORDER BY key
```

## meta

Facts about the database file itself.

|Field|Meaning|
|:-|:-|
|`key`|The name: schema_version; in a public file also public, beta, beta_note, credits, made, races, editions, results, first_year and last_year (what the file holds).|
|`value`|The value. A file whose schema version is newer than the tool's is refused.|

Example, the schema version:

```sql
SELECT key, value FROM meta
```

## v_team_points (view)

team_rank with the season and date of its ranking: the easy way to ask about team points.

|Field|Meaning|
|:-|:-|
|`season`|The season.|
|`date`|The ranking date.|
|`team_id`|UCI's team number.|
|`code`|The team's code that season.|
|`name`|The team's name that season.|
|`nation`|The team's nation.|
|`rank`|Position on that date.|
|`points`|UCI World Ranking points, season to date.|

Links: Group by team_id to follow one team through renames.

Example, one team over the years (uae has uci team number 595):

```sql
SELECT season, date, code, name, rank, points FROM v_team_points WHERE team_id = 595 ORDER BY date
```

## v_results (view)

Every point-scoring result with its race, in one table: the easy way to ask about riders and results.

|Field|Meaning|
|:-|:-|
|`uci_id`|UCI's number for the race edition.|
|`year`|The year.|
|`race`|The race name.|
|`class`|The UCI class.|
|`label`|The section of the page (for example Final Classification).|
|`title`|The result list's title.|
|`rank`|The place.|
|`rider`|The rider.|
|`team`|The team name on the race page.|
|`nation`|The rider's nation.|
|`result`|Time or gap.|
|`points`|UCI points.|
|`race_id`|The race (the same in every year): race.race_id.|
|`race_name`|The race's internal name, the same in every year. The column race is the external name UCI used that year.|
|`age`|The rider's age at the time of the race, as DataRide shows it.|

Example, points per team from the race results:

```sql
SELECT team, SUM(points) AS points, COUNT(DISTINCT uci_id) AS races FROM v_results GROUP BY team ORDER BY points DESC LIMIT 20
```