a schema per era: modelling eighty years of race results

the query was four lines long, it ran in about three milliseconds, and it named the wrong world champion.

select rider, sum(points) as total
from result
where season = 1975 and class = '500cc'
group by rider
order by total desc;

it puts phil read on 96 points at the top. the 1975 500cc world champion is giacomo agostini, on 84.

nothing here is broken. the rows are correct — read really did score 96 points across those ten rounds, agostini really did score 84, and you can check both. the schema is reasonable. the query is the one any of us would write without thinking. the answer is still wrong, because in 1975 only a rider’s best six results counted towards the championship and the other four were discarded. twenty of read’s points were points he was never allowed to keep. wikipedia’s own biography of him puts it plainly: he “actually scored more points than agostini during the season but fell victim to fim scoring rules at the time which only recognized the top six of ten results.” (phil read ↗ , 1975 season ↗ )

i work on race management technology for a living, so i see this shape of problem from close range. everything concrete below is public record, deliberately — that is the useful part. you can check every number in this post, and you can watch the exact moment a query stops answering the question you thought you asked.

the query does not name the wrong champion once

if 1975 were a single quirk you could special-case it and move on. it is not.

run the same naive sum over the first season of the championship, 1949, when six rounds were held and only your best three counted:

sum(points) makes nello pagani the first-ever 500cc world champion and pushes the actual champion down to third, behind a rider who finished third in reality too. brembo’s own history of the championship says the same thing from the other direction: pagani lost “the world title by one point to graham: without discards, he would have been the champion, having scored 9 more points” — and 40 − 31 is exactly 9. (1949 season ↗ , nello pagani ↗ , brembo ↗ )

and when the naive query does get the champion right, it still lies about the season. in 1967 agostini and mike hailwood finished tied on 46 points each, and the title was decided on countback — five wins apiece, so it fell to second places, three to two. sum the raw results and the tie evaporates into a comfortable 58–52. (1967 season ↗ )

my favourite is 1968. agostini started ten premier-class rounds and won all ten. under the 8-6-4-3-2-1 system in force, that is 80 points. his championship total was 48, because only six results counted. he threw away 40% of everything he scored in a season he never once lost. (1968 season ↗ )

so: three different failures from one sum(). the wrong winner, the wrong margin, and a number that is not comparable to any other number in the same column.

the rules moved, and the rows did not

here is what actually sits underneath eighty years of that table, in the premier class alone (full table ↗ ):

a few things worth pulling out of that, because each one kills a different shortcut.

a win has been worth 8, 15, 20 and 25 points. any order by sum(points) across eras is ranking rulebook generosity and calendar length, not riders.

the fastest-lap bonus existed for exactly one season. 1949 awarded a point for it, and no season since has. one row in eighty years needs a column nothing else uses.

1992 lasted one year. the championship went from fifteen scorers to ten and back again, with a different scale in between — “this system would last for only the 1992 season”. (1992 season ↗ ) an era table with tidy decade-shaped ranges will quietly absorb 1992 into its neighbours and you will never see it.

and since 2023 there are two races per round. the saturday sprint pays 12-9-7-6-5-4-3-2-1 to the top nine, into the same riders’ championship, so a single event can now yield 37 points to one rider. (motogp.com ↗ ) “a result” stopped being one row per rider per event, which is a change to the grain of the table, not to its values.

then there is the rule that is not about points at all. only a rider’s best n results counted, from 1949 to 1976, and n was not a constant — for most of that stretch it was derived from the number of rounds, so it moved when the calendar moved, and it differed per class within the same season. in 1968 it was best 3 in 50cc, best 5 in 125cc, best 6 in 250cc and 500cc, and best 4 in 350cc. (1968 season ↗ )

that is the one that breaks the naive schema hardest, and i want to be precise about why: it is not a property of a result row. the points for finishing second are a fact about that row. whether that second place counted is a fact about the whole season, and it is only knowable once every other round has been run.

the schema everybody writes first

create table result (
    season   int  not null,
    round    int  not null,
    class    text not null,
    rider_id int  not null,
    position int,
    points   int  not null,
    primary key (season, round, class, rider_id)
);

this is fine. i would probably write it too. it has exactly one flaw, and the flaw is that points is a stored answer to a question whose rules are not in the database.

whoever loaded 1975 into that table had to know the 1975 rulebook. that knowledge went into an import script, or a spreadsheet, or somebody’s head, and then it left the building. the table now contains a number that is correct, unexplained, and — as we have seen — not summable.

the reflex fix is to add a year column and branch on it in application code. that is the same bug wearing a hat. the rule still is not in the database, it is now in a switch statement, and it is enforced by whoever remembers to update it. an invariant a person has to remember is not an invariant, it is a hope. i have written about what that costs when the thing being forgotten is a lock.

the four options, and what each one costs

versioned rule tables. the rulebook becomes data with a validity range, and points become derived rather than stored.

create table scoring_system (
    id         int  primary key,
    class      text not null,
    valid_from int  not null,
    valid_to   int              -- null = still in force
);

create table scoring_award (
    system_id int          not null references scoring_system(id),
    position  int          not null,
    points    numeric(4,1) not null,
    primary key (system_id, position)
);

numeric, not int, and that is not fussiness: a race stopped before half distance pays half points today, so 12.5 is a real value the schema has to hold. (motogp.com ↗ )

cost: every read grows a join, and loading the rulebook is real archival work. benefit: the rule is now checkable, and adding 2027 is an insert rather than a deploy.

era-scoped views. one view per rulebook era, unioned into a compatibility view. cheap, readable, and genuinely useful on top of rule tables. on its own it fails for the same reason the year column does — the rule lives in view definitions, which is code, which is the thing that gets forgotten. use it as a presentation layer, not as the source of truth.

event-sourcing the rule changes. store the amendments themselves and fold them to get the rulebook as at any date. this is heavier than it sounds, and most of the time you do not need it. you need it when the question is “what did the standings say on the evening of the 3rd?” — regulators, auditors and anyone who has to defend a published table have that question. everyone else does not.

bitemporal columns. worth separating clearly, because it answers a different question. the ones above deal with the rules changing. bitemporality deals with the results changing — and results really do change after the fact. in march 2026 a moto2 round at buriram had a lap incorrectly added to its race distance; the error was found afterwards, the distance actually completed fell below half, and half points had to be awarded retroactively, revising a published championship table. (motomatters ↗ , cycle news ↗ ) if you must reproduce what you published and what is now true, you need valid time and transaction time, and you should adopt it deliberately, because it doubles the width of every predicate you will ever write.

my recommendation: versioned rule tables as the source of truth, era views over the top, and bitemporality only on the tables that get amended. event-sourcing when someone can name the audit that requires it, and not before.

the part the rule table does not solve on its own

the awards table gives you points per position. it does not give you 1975, because best-six is an aggregation rule.

the instinct is to bolt counted_results int onto scoring_system and move on. then you hit 1976, where the rule was best 3 of the first five rounds plus best 3 of what remained (1976 season ↗ ) — a season split into two buckets, each with its own allowance. one integer cannot say that.

so model the bucket, not the number:

create table scoring_window (
    system_id   int not null references scoring_system(id),
    seq         int not null,  -- 1976 has two; every other era has one
    first_round int not null,
    last_round  int,           -- null = to the end of the season
    counted     int,           -- null = every result in this window counts
    primary key (system_id, seq)
);

every era becomes one window with counted set or null. 1976 becomes two rows. nothing is special-cased, and the standings query stops caring which era it is looking at:

with scored as (
    select r.season, r.class, r.rider_id, w.seq, a.points,
           row_number() over (
               partition by r.season, r.class, r.rider_id, w.seq
               order by a.points desc, r.round
           ) as rank_in_window
      from result r
      join scoring_system s
        on r.class = s.class
       and r.season between s.valid_from and coalesce(s.valid_to, 9999)
      join scoring_window w
        on w.system_id = s.id
       and r.round between w.first_round and coalesce(w.last_round, 9999)
      join scoring_award a
        on a.system_id = s.id
       and a.position  = r.position
     where r.position is not null
)
select season, class, rider_id, sum(points) as championship_points
  from scored
  join scoring_window w2 using (seq)
 where w2.counted is null or rank_in_window <= w2.counted
 group by season, class, rider_id;

that query gets 1975 right, gets 1993 right, and gets 1976 right, without knowing that any of them are different. that is the whole goal.

the class column is lying too

where class = '500cc' looks like a stable key. it is a label that has been reused, retired and redefined (overview ↗ ):

• 500cc → motogp in 2002 — and 2002 is not a cutover. both 500cc two-strokes and four-strokes up to 990cc were legal in the same class in the same season. one label, two formulas, one championship table.
• 250cc → moto2 in 2010 and 125cc → moto3 in 2012 were not renames at all. they were new technical formulas, and moto2 changed engine supplier again in 2019.
• 350cc ran from 1949 to 1982 and then simply stopped. 50cc became 80cc in 1984 and was dropped after 1989. sidecars left the championship after 1996.
• and the displacement inside the motogp label went 990 → 800 → 1000, with 850cc scheduled for 2027.

so “is a moto2 win the same achievement as a 250cc win?” is not a data question, it is an editorial one — and the schema’s job is to let you answer it either way rather than deciding for you by silently sharing a string. keep the label a label, give the competition a surrogate identity, and record lineage between them explicitly. then “all premier-class wins” and “all wins in this exact formula” are both one query, and neither is a lie.

there is also a category of round that exists but does not count. after heavy rain shortened the 500cc race at the 1954 ulster grand prix, the fim excluded it as a points-scoring round — a race that happened, has results, and must not be summed. (footnoted in the points-systems table ↗ ; note that the modern response to the same situation is half points, not deletion, so the handling of shortened races is itself era-dependent.)

the test that would have caught all of it

this is the part i would fight for in review. a rulebook you cannot verify is a rulebook you are guessing at, and the historical record hands you the answer key: the champions are known.

-- every season's computed champion must equal the recorded one.
-- returns zero rows, or you have a bug in the rules.
select c.season, c.class, c.rider_id as recorded, s.rider_id as computed
  from champion c
  left join lateral (
      select rider_id
        from standings          -- the view over the query above
       where season = c.season and class = c.class
       order by championship_points desc
       limit 1
  ) s on true
 where c.rider_id is distinct from s.rider_id;

point that at eighty seasons and it fails loudly on 1949 and 1975 the moment your windows are wrong, on 1992 the moment your era boundaries are lazy, and on 1967 as soon as you add the countback tiebreak it needs. it turns “we think we loaded the rules correctly” into something a build can answer.

it also catches the thing i did not expect. while checking the numbers for this post i found that wikipedia’s summary table of scoring systems ↗ says all results counted in 1991, while wikipedia’s own article on the 1991 season ↗ says that “in a one-year quirk, only 13 races counted as, competitors were allowed to drop their two worst scores.” they cannot both be right. your reference data has metadata about itself, and that metadata can be wrong — which is a good reason to derive standings from rules and check them against recorded outcomes, rather than trusting either one alone.

none of this is about motorcycles

strip the sport out and the shape is completely ordinary. a decade of orders where the tax rate, the rounding rule and the definition of “net” all moved. a commission plan rewritten every january, applied to deals that close across the boundary. headcount reports across three reorgs where the department names were reused. an sla whose “response time” started excluding queue time in 2021.

every one of them has the same three symptoms: a sum() that is arithmetically perfect and semantically wrong, an aggregate rule that is not a property of any single row, and a label that was quietly reused. motorsport is just unusually honest about it, because the correct answer is written down and everyone can see when you get it wrong.

if your business had produced a public list of who won every year, you would have found your version of the 1975 bug years ago.

what i’d take from this

• if the rules that produced a number are not in the database, the number is not summable. it is a comment that happens to be numeric.
• put the rulebook in tables with validity ranges, and derive rather than store. adding next year’s rules should be an insert.
• model the bucket, not the count. the first weird era you meet — and there is always a 1976 — will cost you a migration if you stored an integer.
• separate the two time axes. rules changing and results changing are different problems; adopt bitemporality on purpose, on the tables that need it, not everywhere.
• a reused label is not a key. give the thing an identity and record lineage between labels explicitly.
• and find your answer key. the most valuable thing about eighty years of race results is not the results, it is that somebody already published who won. write the query that compares your computation to the published truth, and run it in ci. otherwise the invariant is that everyone remembers the 1975 rulebook — and that is not an invariant, it is a hope.