The better_labels Lookup Tables

Many FIT fields (sport type, event type, dive alarm type, and dozens more) are stored as raw numeric codes, not text. Cordelia's own UI shows you a human-readable, localized name for these; a report script querying the SQLite database directly gets the raw code and needs to resolve it itself. Throughout the Data Dictionary, Full Field Reference, any column that needs this is marked: "The localized text equivalent of this value can be obtained by lookup into better_labels_lookup." This page explains what that lookup actually looks like.

Why the codes stay raw

Cordelia does not bake resolved, localized text into your activity tables at import time. The raw code is the Garmin-documented identifier: comparable across any FIT-based tool, and stable whatever locale Cordelia was running in when the file was imported. Translated text instead lives in two small, normalized tables that every activity table's code columns point into.

The two tables

better_labels_definitions assigns a stable, database-internal better_labels_id to each (domain, garmin_key) pair. domain names a family of related codes (sport, event_type, dive_alarm_type, and so on), and garmin_key is the raw code as it appears in that family's column, stored as text. better_labels_lookup then holds the actual display text: one row per better_labels_id per locale.

CREATE TABLE better_labels_definitions (
    better_labels_id INTEGER PRIMARY KEY,
    domain           TEXT NOT NULL,
    garmin_key       TEXT NOT NULL,
    UNIQUE (domain, garmin_key)
);

CREATE TABLE better_labels_lookup (
    better_labels_id INTEGER NOT NULL REFERENCES better_labels_definitions(better_labels_id),
    locale           TEXT NOT NULL,
    value            TEXT NOT NULL,
    PRIMARY KEY (better_labels_id, locale)
);

The two statements above are an excerpt, re-aligned for readability, of the schema published in full at The schema as SQL. That download is the authoritative text; if the two ever disagree, the download is right.

locale is one of en, fr, es, de, it, ptbr: the same six languages Cordelia's UI supports. Both tables are seeded once, when Cordelia creates the database, and cover every code Cordelia currently recognizes, not just the ones your imported files happen to use.

Finding the domain for a column

The Data Dictionary's field-remark tells you a column needs resolving, but not which domain string to join against; that isn't always the column name. For example, Session.sport_id resolves against domain sport and Session.sub_sport_id against sub_sport (the _id suffix is dropped), but Session.trigger resolves against session_trigger (the table name is prefixed on instead). Rather than guess, list what's actually in your database:

SELECT DISTINCT domain FROM better_labels_definitions ORDER BY domain;
domain
---------
sport
sub_sport
...

A real database has around sixty domains. Once you know the domain name, a quick look at a few of its rows confirms which raw values it covers:

SELECT garmin_key, better_labels_id FROM better_labels_definitions
WHERE domain = 'sport' ORDER BY CAST(garmin_key AS INTEGER) LIMIT 5;

A basic JOIN

Resolving one column is a two-table join: better_labels_definitions to turn the raw code into a better_labels_id, then better_labels_lookup to turn that into text for the locale you want.

SELECT
    s.RecordNumber,
    s.sport_id,
    l.value AS sport_name
FROM Session s
LEFT JOIN better_labels_definitions d
    ON d.domain = 'sport' AND d.garmin_key = CAST(s.sport_id AS TEXT)
LEFT JOIN better_labels_lookup l
    ON l.better_labels_id = d.better_labels_id AND l.locale = 'en';
RecordNumber  sport_id  sport_name
------------  --------  ----------
1             1         Running
2             2         Cycling
3             99

garmin_key is TEXT while a code column like sport_id is usually INTEGER; the explicit CAST(... AS TEXT) makes the comparison unambiguous rather than relying on SQLite's column-affinity rules to coerce it implicitly.

Use LEFT JOIN, not INNER JOIN. A raw code that isn't yet in better_labels_definitions, an unrecognized or newly-added FIT value, has no matching row. An INNER JOIN would silently drop that activity's row from your results entirely; a LEFT JOIN keeps it, with sport_name simply NULL (as row 3, sport_id 99, shows above).

More than one locale, or more than one column, at once

Each additional locale is another join against better_labels_lookup under its own alias:

SELECT
    s.RecordNumber,
    s.sport_id,
    en.value AS sport_en,
    fr.value AS sport_fr,
    es.value AS sport_es
FROM Session s
LEFT JOIN better_labels_definitions d
    ON d.domain = 'sport' AND d.garmin_key = CAST(s.sport_id AS TEXT)
LEFT JOIN better_labels_lookup en ON en.better_labels_id = d.better_labels_id AND en.locale = 'en'
LEFT JOIN better_labels_lookup fr ON fr.better_labels_id = d.better_labels_id AND fr.locale = 'fr'
LEFT JOIN better_labels_lookup es ON es.better_labels_id = d.better_labels_id AND es.locale = 'es';
RecordNumber  sport_id  sport_en  sport_fr       sport_es
------------  --------  --------  -------------  --------
1             1         Running   Course à pied  Correr
2             2         Cycling   Cyclisme       Ciclismo
3             99

And each additional code column is another definitions/lookup pair, joined under its own aliases:

SELECT
    s.RecordNumber,
    sport.value    AS sport,
    sub_sport.value AS sub_sport,
    s.total_distance
FROM Session s
LEFT JOIN better_labels_definitions sport_def
    ON sport_def.domain = 'sport' AND sport_def.garmin_key = CAST(s.sport_id AS TEXT)
LEFT JOIN better_labels_lookup sport
    ON sport.better_labels_id = sport_def.better_labels_id AND sport.locale = 'en'
LEFT JOIN better_labels_definitions sub_sport_def
    ON sub_sport_def.domain = 'sub_sport' AND sub_sport_def.garmin_key = CAST(s.sub_sport_id AS TEXT)
LEFT JOIN better_labels_lookup sub_sport
    ON sub_sport.better_labels_id = sub_sport_def.better_labels_id AND sub_sport.locale = 'en';
RecordNumber  sport    sub_sport      total_distance
------------  -------  -------------  --------------
1             Running  Trail Running  10450.2
2             Cycling                 32000.0
3                                     5000.0

This pattern, one definitions/lookup join pair per code column, one extra lookup alias per locale, covers every table in the Data Dictionary marked as needing resolution; only the domain string and the column being joined change.

For worked examples against a real Cordelia database, see antonio, or the Example Cordelia Databases to try these queries yourself. See also The Cordelia Generated SQLite Database and Editing the Data with Other Apps for opening the database with a general SQLite tool.

See also