ZippyOddsAPI

BlogModels and analytics

How to Design a Sports Betting Database for Odds

Published · 5 min read

A sports betting database for odds needs four tables: events, markets, outcomes, and price observations, where every price row carries the time it was observed. Get the keys right so a moved line becomes a new market, store changes rather than endless copies, and never let a price lose its timestamp.

That's the short version. The rest of this page is the reasoning, a working schema in PostgreSQL, and the two queries you'll run most: "what's the latest price" and "what was the closing price".

Start from what a price actually is

A price only means something with five things attached: which game, which book, which market at which line, which side, and when it was true. Leave any of them out and you'll get wrong answers later without noticing.

The line is the one people get wrong. A total at 224.5 and a total at 225.5 aren't one market whose number changed. They're two bets. If you store them under one key, a line move looks like a big price move, and every chart and closing-line figure you build on it is off. Make the line part of the market's identity.

Time is the other. There are two clocks worth storing for every observation: when the price was observed (or when the cached copy you were served was made) and when you received it. They differ more often than you'd think, and the first one is the one your freshness checks should use.

A schema that holds up

CREATE TABLE events (
  event_id     text PRIMARY KEY,
  league       text NOT NULL,
  home_team    text NOT NULL,
  away_team    text NOT NULL,
  start_time   timestamptz NOT NULL
);

CREATE TABLE markets (
  market_id    text PRIMARY KEY,   -- one book's market at one line
  event_id     text NOT NULL REFERENCES events,
  book         text NOT NULL,
  market_key   text NOT NULL,      -- moneyline, spread, total
  period       text NOT NULL,      -- full_game, 1h, 1q ...
  line         numeric             -- null on a moneyline
);

CREATE TABLE outcomes (
  market_id    text NOT NULL REFERENCES markets,
  outcome_id   text NOT NULL,
  side         text NOT NULL,      -- home, away, draw, over, under
  point        numeric,
  PRIMARY KEY (market_id, outcome_id)
);

CREATE TABLE price_observations (
  market_id          text NOT NULL,
  outcome_id         text NOT NULL,
  price              integer,      -- American odds
  liquidity          numeric,      -- null when the venue doesn't publish it
  first_observed_at  timestamptz NOT NULL,
  last_observed_at   timestamptz NOT NULL,
  received_at        timestamptz NOT NULL,
  PRIMARY KEY (market_id, outcome_id, first_observed_at),
  FOREIGN KEY (market_id, outcome_id) REFERENCES outcomes
);

A few choices in there are deliberate:

  • The outcome key includes the market. If your source gives the same side the same id at every book, an outcome id alone isn't unique. Pairing it with the market id is.
  • Price is nullable. Some pick'em lines have no price, only a payout table. A null is honest. A zero is a bug waiting to happen.
  • Liquidity is nullable. Most sportsbooks don't publish how much you can bet. Unknown isn't the same as zero, and it isn't unlimited either.

If you also keep team and player stats, put them in their own tables keyed by the same event_id. A sports betting stats database and an odds store answer different questions, and joining them on one event key is far easier than untangling one wide table later.

Snapshots or a change log

There are two ways to fill price_observations.

Snapshots write every price on every read. They're simple and they make "what did the board look like at 6pm" a trivial query. They're also enormous. Say you track 2,000 outcomes and read them every 60 seconds. That's 2,000 × 1,440 = 2,880,000 rows a day, most of them repeats.

A change log writes a new row only when the price changes, and otherwise stretches the current row's last_observed_at. If those same outcomes change 20 times a day each, that's about 40,000 new rows a day, and you've lost nothing: the row still tells you the price held from its first observation to its last.

The second clock is what makes the change log safe. Without last_observed_at, a price that hasn't changed and a price you stopped reading look identical. With it, you can tell a stable price from a dead feed.

De-duplication and ordering

Your reads will overlap, arrive out of order and repeat. Three rules keep the table clean:

  1. Key on observation time, not receipt time. Two copies of the same cached page carry the same observation time, so the primary key rejects the second one.
  2. Never let an older observation overwrite a newer one. If a read's observation time is earlier than the latest row you hold for that outcome, drop it.
  3. Extend, don't duplicate. If the price matches the latest row, update last_observed_at instead of inserting.

In SQL, per incoming price, inside one transaction:

-- $1 market_id, $2 outcome_id, $3 price, $4 observed_at, $5 received_at
UPDATE price_observations p
SET last_observed_at = $4
WHERE p.market_id = $1 AND p.outcome_id = $2
  AND p.price IS NOT DISTINCT FROM $3
  AND p.last_observed_at < $4
  AND p.first_observed_at = (
    SELECT max(first_observed_at) FROM price_observations
    WHERE market_id = $1 AND outcome_id = $2);

If that updates no row, and $4 is later than the newest last_observed_at you hold for the outcome, insert a new row with first_observed_at and last_observed_at both set to $4.

The closing price query

Closing prices are the reason many people build a betting database in the first place. The closing price is the last price observed before the start:

SELECT DISTINCT ON (o.market_id, o.outcome_id)
       o.market_id, o.outcome_id, o.price AS closing_price,
       o.last_observed_at
FROM price_observations o
JOIN markets m ON m.market_id = o.market_id
JOIN events  e ON e.event_id  = m.event_id
WHERE o.first_observed_at < e.start_time
ORDER BY o.market_id, o.outcome_id, o.first_observed_at DESC;

Check last_observed_at on the result. If the last observation was an hour before the start, that's not a closing price, it's the last one you happened to see. Flag it rather than use it.

Filling it from ZippyOddsAPI

The schema above maps directly onto ZippyOddsAPI's ids. An evt_ id is one real game joined across sources, so it's your event_id. An mkt_ id is one book's market, and the line is part of it, so a total that moves gets a new id, which is exactly the rule this schema wants. A sel_ id is the same for the same side and point at every book, which is why the outcome key pairs it with the market. The docs say to treat ids as opaque strings, so store them as text and don't parse them.

For the clocks, an outcome carries its own observed_at and received_at when the selection has its own copy clock, and each book carries updated_at, which is never fresher than the prices it wraps. ZippyOddsAPI also keeps a price history of its own: /v1/history returns a market's open, current and every stored change for 30 days, for full-game moneyline, spread and total markets. If you want those past 30 days, this table is where they go. Field names and limits are in the API docs.

What to build on top

A store like this is the base for everything else. The guide to sports betting data covers which kinds of data are worth collecting and how to judge a source. Once prices and results are in, sports betting analytics shows how to measure hold and closing line value from them, and a sports betting model is only as good as the price history you test it against.