The SDX database contains sports data: competitions, events, teams, people, venues, and how they connect. It is a standard SQLite database, so you can open it with the sqlite3 command line tool, a GUI such as DB Browser for SQLite, or the SQLite library in almost any programming language.

This article explains how the data is organized so you can query it with confidence and join it to your own systems.

The big picture

The database is organized around a few kinds of tables:

Entity tables hold one row per real-world thing: a sport, a competition, an event, a venue, and so on.
Participant tables hold the teams and people that take part in competitions and events, in a single unified list.
Relationship (junction) tables connect entities together, such as which venues host a competition.
Supporting tables hold extras like social media handles and websites, plus a _metadata table describing the file itself.

Every row in an entity table is identified by an SDX ID, which is the key that ties everything together.

SDX IDs

Every entity has a permanent, globally unique identifier called an SDX ID. It has two parts joined by an underscore: a short prefix that tells you what kind of entity it is, and a fixed-length 22-character code made of letters and numbers (0-9, A-Z, a-z). That code is a standard UUID (version 4) encoded in base62, so every SDX ID is unique and carries the same 128 bits of information as a UUID, in 22 characters instead of 36.

Entity	Prefix	Example
Sport	`sport_`	`sport_2HrG5w6F4XEbyyWZMyH0yB`
Competition	`comp_`	`comp_1RlFhx09QJRaAKHzOM95pA`
Event	`event_`	`event_5drIR5mfqNgtB1SiIRntUy`
Media event	`mevent_`	`mevent_6vQEyuZzssE1GQ0JJEpkEL`
Team	`team_`	`team_1CYR2BNybR1dpgHBbsFxsh`
Person	`person_`	`person_1dKyDLAnLm5LUmhZTqgxZv`
Venue	`venue_`	`venue_4HRDfiUfc24zIViagk2ZH7`

A few things worth knowing:

SDX IDs never change. You can safely store them in your own systems as permanent references.
The database has no internal numeric IDs. The SDX ID is the only identifier, and it is the primary key of every entity table.
Columns that point to another entity are named sdx_<something>_id (for example, sdx_competition_id) and always contain an SDX ID.
Because the prefix identifies the entity type, you can tell what an ID refers to without looking it up.
SDX IDs are case-sensitive, always 22 characters after the prefix, and safe to use in URLs, file names, and logs without escaping.
The 22-character code can be converted back to the original UUID at any time, so you can store the SDX ID as-is.

Tables at a glance

Table	Type	What it contains
`sports`	Entity	The sports covered, such as Basketball or Soccer
`competitions`	Entity	Leagues, tournaments, and other competitions
`events`	Entity	Individual games, matches, or fixtures
`media_events`	Entity	Broadcast-oriented names for events
`venues`	Entity	Stadiums, arenas, and other places events are played
`participants`	Entity	Teams and people in one unified list
`competition_participants`	Relationship	Which teams and people belong to which competition
`competition_venues`	Relationship	Which venues are used by which competition
`event_teams`	Relationship	Which teams play in which event
`event_participants`	Relationship	Which teams and people take part in which event
`media_event_events`	Relationship	Links media events to events
`digital_footprint`	Supporting	Social media handles and websites
`_metadata`	Supporting	Information about this file: when it was built and what it contains

Not every table is in every file. Which tables you receive depends on the data included in your file. sports and competitions are always present; the others appear only when your data includes them. See Checking the contents of your file below.

How the entities relate

Sport
  └── Competition
        ├── Event ──── Venue
        │     ├── Teams (event_teams)
        │     ├── Participants (event_participants)
        │     └── Media event (media_event_events)
        ├── Participants: teams and people (competition_participants)
        └── Venues (competition_venues)

Each competition belongs to exactly one sport.
Each event belongs to exactly one competition and optionally has one venue.
Competitions and events can have many participants, and a team or person can appear in many competitions.
A media event can be linked to one or more events.

Table reference

Core entities

sports

Column	Type	Description
`sdx_id`	TEXT	Primary key. The SDX ID of the sport.
`name`	TEXT	Sport name, for example Basketball.

competitions

Column	Type	Description
`sdx_id`	TEXT	Primary key. The SDX ID of the competition.
`name`	TEXT	Competition name, for example National Basketball Association.
`sdx_sport_id`	TEXT	The sport this competition belongs to. Joins to `sports.sdx_id`.
`gender`	TEXT	Gender of the competition, such as Male or Female. May be empty.
`competition_type`	TEXT	The kind of competition, such as League. May be empty.
`status`	TEXT	Whether the competition is active, such as Active.
`friendly_name`	TEXT	A display name that adds context, for example National Basketball Association [USA]. May be empty.
`abbreviation`	TEXT	Short name, for example NBA. May be empty.
`tier`	TEXT	Level of the competition within its sport. May be empty.
`aliases`	TEXT (JSON)	Other names the competition is known by. See Data conventions.
`country`	TEXT	Country name. May be empty.

events

Column	Type	Description
`sdx_id`	TEXT	Primary key. The SDX ID of the event.
`name`	TEXT	Event name, typically the matchup.
`start_date`	TEXT	Calendar date of the event in `YYYY-MM-DD` format. May be empty.
`sdx_competition_id`	TEXT	The competition this event belongs to. Joins to `competitions.sdx_id`.
`sdx_venue_id`	TEXT	The venue where the event is played. Joins to `venues.sdx_id`. May be empty.
`aliases`	TEXT (JSON)	Other names for the event.

media_events

Column	Type	Description
`sdx_id`	TEXT	Primary key. The SDX ID of the media event.
`name`	TEXT	Name as commonly used in media, for example Philadelphia 76ers at Boston Celtics.

venues

Column	Type	Description
`sdx_id`	TEXT	Primary key. The SDX ID of the venue.
`name`	TEXT	Venue name, for example TD Garden.
`venue_type`	TEXT	Kind of venue, such as Sport Hall. May be empty.
`country`	TEXT	Country name. May be empty.
`city`	TEXT	City. May be empty.
`capacity`	INTEGER	Seating capacity. May be empty.
`longitude`	REAL	Longitude in decimal degrees. May be empty.
`latitude`	REAL	Latitude in decimal degrees. May be empty.
`aliases`	TEXT (JSON)	Other names for the venue.

Participants

participants

Teams and people share one table. This makes it easy to work with anything that can take part in a competition or event, without caring whether it is a team or an individual.

Column	Type	Description
`sdx_id`	TEXT	Primary key. A `team_` or `person_` SDX ID.
`participant_type`	TEXT	Either `team` or `person`.
`name`	TEXT	Name of the team or person.
`country`	TEXT	Country name. For a person with more than one country, the names are separated by commas. May be empty.

competition_participants

Column	Type	Description
`sdx_competition_id`	TEXT	Joins to `competitions.sdx_id`.
`sdx_participant_id`	TEXT	Joins to `participants.sdx_id`.
`role`	TEXT	`team` for teams. For people, their role in the competition, such as `athlete`.

Relationship tables

competition_venues

Column	Type	Description
`sdx_competition_id`	TEXT	Joins to `competitions.sdx_id`.
`sdx_venue_id`	TEXT	Joins to `venues.sdx_id`.

event_teams

Column	Type	Description
`sdx_event_id`	TEXT	Joins to `events.sdx_id`.
`sdx_team_id`	TEXT	Joins to `participants.sdx_id` (where `participant_type = 'team'`).

event_participants

Column	Type	Description
`sdx_event_id`	TEXT	Joins to `events.sdx_id`.
`sdx_participant_id`	TEXT	Joins to `participants.sdx_id`.
`role`	TEXT	`team` for teams. For people, their role in the event, such as `athlete`.

Participant lists for an event are not available for every competition. If a competition has no rows in this table, use event_teams or check the competition's events directly.

media_event_events

Column	Type	Description
`sdx_media_event_id`	TEXT	Joins to `media_events.sdx_id`.
`sdx_event_id`	TEXT	Joins to `events.sdx_id`.

Supporting tables

digital_footprint

Social media accounts and websites for competitions, teams, people, and venues.

Column	Type	Description
`entity_type`	TEXT	One of `competition`, `team`, `person`, or `venue`.
`sdx_entity_id`	TEXT	The SDX ID of the entity this entry belongs to.
`platform`	TEXT	The platform, such as `instagram` or `website`.
`handle`	TEXT	The account handle for social platforms. Empty for websites.
`url`	TEXT	The web address for websites. Empty for most social entries.

Every row has a handle, a url, or both.

_metadata

A small key/value table describing the file.

Key	Description
`exported_at`	When the file was built (UTC).
`total_rows`	Total number of rows across all data tables.
`competition_sdx_ids`	JSON list of the competitions included in the file.
`in_scope_entities`	JSON list of the kinds of data included in the file.
`export_level_by_competition`	JSON object showing which kinds of data are included for each competition.
`skipped_competitions`	Competitions that could not be included in this build, if any.

Data conventions

Lists are stored as JSON text. Columns such as aliases contain a JSON array like ["C's", "Celtics", "Celts"]. In SQLite you can read them with the built-in JSON functions, for example json_each(aliases).
Dates are plain dates. events.start_date is YYYY-MM-DD, with no time or time zone.
Missing values are NULL. Optional columns are empty (NULL) when we do not have a value. Do not treat an empty value as zero or as "none".
Names are for people, IDs are for systems. Names can be spelled several ways and can change over time, so always use sdx_id to match and join records.

Checking the contents of your file

Because the contents of the database can vary, two things can be true of your file:

A table may be missing. If you have no data of a given kind, its table is not created at all. Check for a table before querying it:SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;
An ID may point to a table you do not have. For example, an event can reference a venue (sdx_venue_id) even if your file contains no venues table. The ID is still valid; the details are just not part of your file. Use a LEFT JOIN so these rows are not dropped.

To see exactly what is included, read the _metadata table:

SELECT key, value FROM _metadata;

Example queries

List the competitions for a sport:

SELECT c.sdx_id, c.name, c.abbreviation
FROM competitions c
JOIN sports s ON s.sdx_id = c.sdx_sport_id
WHERE s.name = 'Basketball'
ORDER BY c.name;

List upcoming events in a competition, with their venues:

SELECT e.start_date, e.name, v.name AS venue, v.city
FROM events e
LEFT JOIN venues v ON v.sdx_id = e.sdx_venue_id
WHERE e.sdx_competition_id = 'comp_1RlFhx09QJRaAKHzOM95pA'
  AND e.start_date &gt;= date('now')
ORDER BY e.start_date;

Find the teams in an event:

SELECT p.name
FROM event_teams et
JOIN participants p ON p.sdx_id = et.sdx_team_id
WHERE et.sdx_event_id = 'event_5drIR5mfqNgtB1SiIRntUy';

Look up a team by an alternate name: team and person aliases are not included in this file, but competition, event, and venue aliases are. For example, to find a venue by any of its names:

SELECT v.sdx_id, v.name
FROM venues v
WHERE v.name = 'Garden'
   OR EXISTS (SELECT 1 FROM json_each(v.aliases) WHERE value = 'Garden');

List the competitions a team plays in:

SELECT c.name
FROM competition_participants cp
JOIN competitions c ON c.sdx_id = cp.sdx_competition_id
WHERE cp.sdx_participant_id = 'team_1CYR2BNybR1dpgHBbsFxsh';

Keeping your data current

The database is a snapshot. The exported_at value in _metadata tells you how fresh your copy is. SDX IDs are permanent, so when you receive a newer file you can match it against your stored IDs, and new records simply appear with new IDs.

Related resources

Questions about your data or this schema? Contact SDX support and include the exported_at value from your file.

Ready to start mapping?