画板 1

    # Athlete Competition Results Platform: Data Modeling, Revision Tracking, and Query Design

    3 hr ago0 views

    This article walks through the end-to-end architecture of an athlete competition results platform: how to split results into manageable domain entities, why the "entrant" concept is essential for supporting both individual and relay events, how to make result changes traceable with revision snapshots and approval workflows, how to externalize ranking and points rules into configuration, how to layer storage and caching for polarized query patterns, and which anti-patterns to avoid. It includes table DDL, consistency-check queries, API conventions, data quality metrics, and a phased rollout plan suitable for international competition data platforms.

    1. Background: The Nature of Results Data and Its Common Pain Points

    In post-race reporting and athlete profiles for international events, you regularly encounter the same kind of structured information:

    • Basic athlete details: name, nationality, date of birth, place of birth;
    • Results listed race by race: discipline, finishing position, and completion time;
    • Relay team rosters and overall team times.

    This data looks trivial. The moment you try to build one unified results platform that simultaneously serves athlete profiles, schedule lookups, live leaderboards, and post-race analysis, several typical problems surface.

    1. Many entities, complex relationships

    A single competition involves events, schedules, disciplines, participating units, athletes, and relay teams. Without first clarifying the boundaries between these entities, the data model spins out of control quickly.

    2. Result facts change

    A result is not immutable once entered. It may need to be revised because of an overturned decision, a protest, a timing correction, or a change in participation status (did not finish, did not start, disqualified, result annulled). Overwrite-style updates destroy the change history, leaving you unable to reconstruct what happened.

    3. Definitions must be unified in one place

    Disciplines differ, and so do their rules. Individual events are ranked on completion time; pursuit races also layer in the starting-time offset; relays are ranked by team. How a rank is produced, how ties are handled, and whether non-finishers participate in ranking all have to be defined once, in one place.

    4. Query patterns are polarized

    Everyday reads are dominated by detail lookups “by athlete, by event, by discipline” — low data volume, high concurrency. During competition periods or major championships, leaderboard and aggregate queries spike sharply. Complex analysis is mostly done offline after the event.

    The rest of this article presents a complete design path around these problems, from modeling through to production.

    2. Domain Modeling: Breaking “Results” into Manageable Entities

    2.1 Entity breakdown

    The core domain can be divided into the following entities:

    Entity Meaning Key attributes
    Competition event One edition of an event (e.g., a given international multi-sport games, a given world championship) Name, level, competition period, host country or region
    Discipline definition Rule parameters for a specific discipline within an event Discipline category, gender, distance, shooting rounds and targets per round, format parameters
    Session A round of a discipline held at a specific time and venue Event, discipline, date, start time, venue and time zone
    Entrant One athlete (individual disciplines) or one relay team (team disciplines) Team name, seed bib number, entrant status
    Team roster Relay team members and their leg assignments Athlete, leg number, substitution sequence
    Athlete The athlete entity itself Full name in local script and Latin script, nationality, date of birth, place of birth, gender
    Result An entrant’s finish result in a given session Rank, net time, start and finish timestamps, shooting details
    Result revision Any change record applied to a result Change type, before/after content, reason, approval information

    The single most important decision here: the “entrant” must be its own table — the result table must not hold a foreign key directly to the athlete table. For individual disciplines the relationship is one-to-one; for relays it is one-to-many, since a team consists of several athletes and each athlete runs only one leg. If the result table has nothing but an athlete_id foreign key, relay results can only hang off a single team member, and team ranking, split results, and substitutions become impossible to express.

    2.2 Illustrative DDL

    The DDL below illustrates the structure; in a real project you will need to adjust indexes, partitioning, and storage engines to the capabilities of your database:

    1. -- Competition event
    2. CREATE TABLE competition_event (
    3.     event_id        BIGINT      PRIMARY KEY,   -- generated by a unified ID-issuing service
    4.     name            VARCHAR(128) NOT NULL,     -- event name
    5.     level           VARCHAR(32)  NOT NULL,     -- e.g. "international multi-sport games", "world championship"
    6.     host_country    CHAR(3)      NOT NULL,     -- three-letter code of the host country or region
    7.     start_date      DATE         NOT NULL,
    8.     end_date        DATE         NOT NULL,
    9.     CONSTRAINT uk_event_name_period UNIQUE (name, start_date)
    10. );
    12. -- Athlete
    13. CREATE TABLE athlete (
    14.     athlete_id      BIGINT      PRIMARY KEY,
    15.     full_name       VARCHAR(64)  NOT NULL,
    16.     full_name_latin VARCHAR(64),               -- Latin-script name, for retrieval and display on the international stage
    17.     nationality     CHAR(3)      NOT NULL,
    18.     birth_date      DATE         NOT NULL,
    19.     birth_place     VARCHAR(128),
    20.     gender          CHAR(1)      NOT NULL,
    21.     status          SMALLINT     NOT NULL DEFAULT 1,  -- 1 = active, 0 = retired/deactivated
    22.     created_at      TIMESTAMP    NOT NULL,
    23.     updated_at      TIMESTAMP    NOT NULL
    24. );
    26. -- Entrant: a single athlete for individual disciplines, a whole team for relays
    27. CREATE TABLE entrant (
    28.     entrant_id      BIGINT      PRIMARY KEY,
    29.     entrant_type    VARCHAR(16) NOT NULL,      -- SOLO / RELAY
    30.     display_name    VARCHAR(128) NOT NULL,
    31.     seed_no         INT,                       -- seed bib number
    32.     athlete_id      BIGINT      NULL,          -- populated only for SOLO
    33.     FOREIGN KEY (athlete_id) REFERENCES athlete(athlete_id)
    34. );

    2.3 Why athlete information also needs versioning

    Date of birth, place of birth, and nationality are relatively stable but still change over time — for example administrative adjustments to a place of birth, or a change of nationality. Use a “main table plus change-history table” approach: the main table holds the current valid values, and the history table appends the before/after content, timestamp, and reason for every change. This keeps queries against the main table returning the latest information while allowing athlete profiles to state precisely the point in time the data reflects.

    3. Modeling Relay Events: Teams, Legs, and Split Results

    A biathlon relay has four team members per event, each covering one 7.5 km leg with a handover in between. Disciplines like this impose three additional requirements on the data model.

    1. The rank belongs to the team, not the individual

    Several members of the same team share one rank and one total time. When querying an athlete’s relay result, you look up the team result through that athlete’s leg — you do not duplicate the same rank onto every member, otherwise medal counts per athlete get inflated.

    2. Split results must be separable and combinable

    Split net times drive intra-team leg comparison and handover positions, but “the sum of split net times” usually does not equal the team total. Each leg’s timing starts at the handover moment from the previous leg, the team total is measured at the moment the last member crosses the line, and the time spent skating inside the exchange zone may be included along the way. Store the total time and the per-leg handover timestamps on two tracks, and derive leg durations by subtracting adjacent handover timestamps. Do not spread the total evenly across legs.

    3. The roster can change mid-event

    Athlete substitutions due to injury or feeling unwell during warm-up are common in team disciplines. The roster table needs to record the substitution sequence and the effective leg, and preserve a complete change history so that post-race result confirmation and historical queries stay consistent.

    The illustrative schema looks like this:

    1. -- Relay team roster (change_seq distinguishes multiple entries/substitutions for the same team)
    2. CREATE TABLE relay_team_roster (
    3.     team_id         BIGINT      PRIMARY KEY,
    4.     entrant_id      BIGINT      NOT NULL,
    5.     athlete_id      BIGINT      NOT NULL,
    6.     leg_no          SMALLINT    NOT NULL,     -- leg 1..4
    7.     change_seq      SMALLINT    NOT NULL DEFAULT 0,  -- substitution sequence
    8.     FOREIGN KEY (entrant_id) REFERENCES entrant(entrant_id),
    9.     FOREIGN KEY (athlete_id) REFERENCES athlete(athlete_id),
    10.     CONSTRAINT uk_team_leg UNIQUE (entrant_id, leg_no, change_seq)
    11. );
    13. -- Relay split results (based on handover timestamps, derivable from the main result table)
    14. CREATE TABLE relay_leg_split (
    15.     split_id        BIGINT      PRIMARY KEY,
    16.     entrant_id      BIGINT      NOT NULL,
    17.     athlete_id      BIGINT      NOT NULL,
    18.     leg_no          SMALLINT    NOT NULL,
    19.     exchange_in_ms  BIGINT      NULL,          -- receive-handover timestamp (ms from race start)
    20.     exchange_out_ms BIGINT      NULL,          -- give-handover timestamp
    21.     leg_rank        SMALLINT,                  -- leg ranking by time
    22.     CONSTRAINT uk_leg UNIQUE (entrant_id, leg_no)
    23. );

    Consistency checks: a self-join reveals whether adjacent legs’ handover timestamps line up. A null in_ms means there is no receive record (did not start or did not finish); a mismatch between out_ms and the next leg’s in_ms flags an anomalous record.

    1. -- Find split rows with discontinuous handover timestamps or missing legs
    2. SELECT a.entrant_id,
    3.        a.leg_no                         AS prev_leg,
    4.        a.exchange_out_ms,
    5.        b.leg_no                         AS next_leg,
    6.        b.exchange_in_ms
    7. FROM relay_leg_split a
    8. LEFT JOIN relay_leg_split b
    9.        ON b.entrant_id = a.entrant_id
    10.       AND b.leg_no     = a.leg_no + 1
    11. WHERE a.exchange_out_ms IS NOT NULL
    12.   AND (b.split_id IS NULL OR b.exchange_in_ms <> a.exchange_out_ms);

    To be clear: race-time rankings and handover timestamps come from the timing system’s raw records. The platform only stores, converts, and presents them — it never derives ranks on its own.

    4. Result Revisions and Timelines: Making Fact Changes Traceable

    4.1 Types of revisions

    The most common mistake on a results platform is treating a “result” as an immutable fact. The revision types that occur in practice include:

    Revision type Typical scenario Impact downstream
    Backfill Original timing missing; result entered after the race from video or a timing backup Result changes from empty to populated
    Overturned decision A post-race protest leads to a rank change Ranks cascade; points may change
    Timing correction Uniform correction due to timing-equipment error Time changes; rank usually unchanged
    Participation status change Did not finish, did not start, result annulled, disqualified Whether the record participates in ranking changes
    Roster change Mid-event relay substitution Membership and result attribution change
    Profile correction Updates to place of birth, nationality, and other profile fields Affects profile queries only

    4.2 Table design

    The recommended combination is “results main table + revision detail table + valid-version view”:

    1. CREATE TABLE race_result (
    2.     result_id       BIGINT      PRIMARY KEY,
    3.     session_id      BIGINT      NOT NULL,      -- session
    4.     entrant_id      BIGINT      NOT NULL,      -- entrant
    5.     rank_no         SMALLINT,                  -- current valid rank; null when not ranked
    6.     finish_time_ms  BIGINT,                    -- total time (ms)
    7.     start_time_ms   BIGINT,                    -- start time (ms, used for interval-start disciplines such as pursuit)
    8.     net_time_ms     BIGINT,                    -- net time = total time - start time
    9.     shooting_detail JSON,                      -- per-round hits/misses
    10.     status_code     VARCHAR(16) NOT NULL,      -- FINISHED / DNF / DNS / DSQ
    11.     version_no      INT         NOT NULL DEFAULT 1,
    12.     updated_at      TIMESTAMP   NOT NULL,
    13.     CONSTRAINT uk_result UNIQUE (session_id, entrant_id)
    14. );
    16. CREATE TABLE result_revision (
    17.     revision_id     BIGINT      PRIMARY KEY,
    18.     result_id       BIGINT      NOT NULL,
    19.     revision_type   VARCHAR(32) NOT NULL,
    20.     before_snapshot JSON        NOT NULL,      -- full snapshot before the change
    21.     after_snapshot  JSON        NOT NULL,      -- full snapshot after the change
    22.     reason          VARCHAR(512),
    23.     effective_from  TIMESTAMP   NOT NULL,
    24.     approved_by     VARCHAR(64),               -- reviewer / governing body
    25.     approval_status VARCHAR(16) NOT NULL        -- PENDING / APPROVED / REJECTED
    26. );

    Snapshot-style revision records are used here rather than field-level diffs. The reason: result fields have complex interdependencies — for instance, a rank change cascades into re-ranking other entrants. Storing the full before/after snapshots lets you accurately rebuild the complete leaderboard at any point in time, whereas field-level diffs easily miss things when changes cascade.

    4.3 State machine and approval workflow

    Revisions must go through a state transition so that unconfirmed results are not published externally:

    1. submitted / corrected → pending review → active (visible externally)
    2.                               ↘ rejected

    Key rules:

    • Only approved revisions enter the valid-version view; pending changes are visible internally only;
    • Every revision must record a reason, to support post-race review and data auditing;
    • Any revision that affects ranks must trigger a cascading recalculation — you cannot just change a single row.

    4.4 Cascade recalculation example

    The pursuit is an interval-start discipline: the gap between athletes’ start times comes from the previous discipline’s results, and the final rank is determined by the order of crossing the finish line. Suppose one result is changed from “valid” to “disqualified” — every entrant ranked after it should move up. Illustrative SQL:

    1. -- Pseudo-SQL: freeze the affected scope inside a transaction, then re-rank in one pass
    2. BEGIN;
    3.   UPDATE race_result
    4.      SET status_code = 'DSQ', version_no = version_no + 1
    5.    WHERE result_id = :target_result_id;
    7.   -- Regenerate ranks by ascending net time (ties handled by RANK; simplified to a full re-rank here)
    8.   WITH ranked AS (
    9.       SELECT result_id,
    10.              RANK() OVER (ORDER BY net_time_ms ASC) AS new_rank
    11.         FROM race_result
    12.        WHERE session_id = :session_id
    13.          AND status_code = 'FINISHED'
    14.   )
    15.   UPDATE race_result r
    16.      SET rank_no = k.new_rank, version_no = version_no + 1
    17.     FROM ranked k
    18.    WHERE r.result_id = k.result_id;
    19. COMMIT;

    In real implementations you would normally re-rank only the affected rank interval rather than the whole field. Either way, the work must complete inside a single transaction, and a “result changed” event should be published to a message queue so downstream consumers such as leaderboard caches and points calculation can react.

    5. Rank and Points Calculation: Making the Rules Explicit

    5.1 Rank generation rules

    Rank generation rules differ by discipline and must be configured explicitly in the discipline definition rather than hard-coded:

    • Individual disciplines (individual, sprint, mass start, etc.): compute total or net time according to the rules, then rank ascending; DNF, DNS, and DSQ records do not participate in rank generation but must be retained in the detail;
    • Pursuit: start order and time gaps derive from the previous discipline; the rank is in principle the order of crossing the finish line. If two athletes cross simultaneously, follow whatever official determination basis applies, such as photo finish — the platform must not infer on its own;
    • Relay: the rank belongs to the team; an athlete’s individual rank should be expressed as “the rank of the team they were part of,” to avoid conflating it with the best individual rank in the discipline;
    • Tied ranks: generate with a standard ranking window function — for example, when two athletes tie for 2nd, the next one is 4th, not 3rd.

    5.2 Season points calculation

    Points calculations belong in a separate calculation layer whose input is the valid ranks of each session and whose output is cumulative season points and standings. Illustrative logic:

    1. -- Using a "rank interval -> points" mapping table called points_map
    2. SELECT r.athlete_id,
    3.        SUM(p.points) AS season_points,
    4.        COUNT(*)      AS scored_races
    5.   FROM athlete_race_index r          -- athlete-session link table (with discipline, leg, etc.)
    6.   JOIN race_result      s ON s.result_id = r.result_id
    7.   JOIN points_map       p ON p.discipline_code = r.discipline_code
    8.                          AND r.rank_no BETWEEN p.rank_from AND p.rank_to
    9.  WHERE r.season = :season
    10.    AND s.status_code = 'FINISHED'
    11.  GROUP BY r.athlete_id
    12.  ORDER BY season_points DESC;

    5.3 Presentation definitions: “best individual rank in a discipline” vs. “number of starts”

    An athlete profile page typically presents these dimensions:

    • Best rank at a given event and the corresponding discipline;
    • Individual and team disciplines counted separately;
    • Number of disciplines entered and number finished.

    Illustrative calculation logic:

    1. -- An athlete's best individual rank at a given event (solo entrants only)
    2. SELECT d.name AS discipline_name,
    3.        MIN(s.rank_no) AS best_rank
    4.   FROM race_result  s
    5.   JOIN entrant      e ON e.entrant_id = s.entrant_id
    6.   JOIN event_session g ON g.session_id = s.session_id
    7.   JOIN event_definition d ON d.definition_id = g.definition_id
    8.  WHERE e.entrant_type = 'SOLO'
    9.    AND e.athlete_id = :athlete_id
    10.    AND g.event_id = :event_id
    11.    AND s.status_code = 'FINISHED'
    12.  GROUP BY d.name
    13.  ORDER BY best_rank ASC;

    If relay ranks are to be included in an athlete’s personal best statistics, the definition must be stated explicitly and the discipline type labeled — otherwise readers easily misread a team rank as an individual result.

    6. Data Ingestion and Quality Validation

    6.1 Sources and priority

    Layer the sources and make the priority explicit:

    1. First priority: official results sheets and exports from the official timing system — the authoritative basis for ranks and times;
    2. Second priority: entry systems and qualification/points-race results, used for team rosters, seed information, and career-history completion;
    3. Third priority: curated public sources and publicly available media information, used only to complete profile-type fields, such as place of birth and standardized spelling of the Latin-script name;
    4. Not recommended as a sole source: broadcast captions and scraped live-score pages. They are vulnerable to latency and layout errors, and raise source-compliance questions.

    Records written into the results store should retain a source identifier and collection timestamp so that disputes can be traced back.

    6.2 Three layers of validation

    Layer 1: structural validation

    • Required fields are non-null, such as session, entrant, and status code;
    • Enumerated values are legal, such as status codes and discipline categories;
    • Time fields are valid timestamps and fall within the event period.

    Layer 2: business validation

    • Finished records must have a finish_time_ms greater than 0;
    • A relay team must have four members, with non-duplicated legs covering 1–4;
    • An entrant cannot have two valid results in the same session;
    • A pursuit result must be later than the corresponding sprint result.

    Layer 3: consistency validation

    • The difference between the total time and the sum of the parts is within a threshold;
    • Handover timestamps across splits are continuous;
    • Points aggregates agree with the details;
    • Ranks are continuous with no gaps (apart from jumps caused by ties).

    6.3 Validation task sketch

    1. # Validation pseudo-code: returns an issue list, does not modify data
    2. def validate_result_file(rows, rule_set):
    3.     issues = []
    5.     for idx, row in enumerate(rows):
    6.         # 1) structural validation
    7.         for field in rule_set.required_fields:
    8.             if not row.get(field):
    9.                 issues.append({"row": idx, "level": "ERROR",
    10.                                "code": "MISSING_FIELD", "field": field})
    12.         # 2) business validation
    13.         if row.get("status") == "FINISHED" and not row.get("finish_time_ms"):
    14.             issues.append({"row": idx, "level": "ERROR",
    15.                            "code": "FINISHED_WITHOUT_TIME"})
    17.         # 3) sanity check on time fields
    18.         if row.get("finish_time_ms", 0) < 0:
    19.             issues.append({"row": idx, "level": "ERROR",
    20.                            "code": "NEGATIVE_TIME"})
    22.     # 4) consistency validation: duplicate entrants within the same session
    23.     seen, dup = set(), []
    24.     for row in rows:
    25.         key = (row.get("session_id"), row.get("entrant_id"))
    26.         if key in seen:
    27.             dup.append(key)
    28.         seen.add(key)
    29.     if dup:
    30.         issues.append({"level": "ERROR", "code": "DUPLICATE_ENTRANT", "keys": dup})
    32.     return issues

    6.4 Handling flow

    Validation results split into blocking and warning levels:

    • Blocking issues: must be fixed before the record can be written;
    • Warning-level issues: allowed into the store, with a ticket opened for follow-up.

    Decouple the validation task from the ingestion task and deliver work through a message queue. Validation can then scale independently; failed messages go to a retry queue, and after exceeding the retry limit they land in a dead-letter queue for manual handling.

    7. Storage Tiering and Query Optimization

    7.1 Three storage tiers

    Tier Content Storage recommendation Typical access
    Detail tier Results, revisions, rosters, splits Relational database, partitioned by event or year Point lookups, small-range scans
    Aggregate tier Season points, career statistics, leaderboard snapshots Precomputed result tables or columnar storage Large-range aggregation
    Cache tier Hot athlete profiles, current-session leaderboards In-memory cache plus local cache High-frequency reads

    The core principle of tiering: the detail layer is append-only (revisions are recorded by appending), aggregates are recomputed on demand, and caches have explicit invalidation policies.

    7.2 Key index design

    • (session_id, entrant_id): unique index guaranteeing only one valid result per entrant per session;
    • (athlete_id, event_id, discipline_code): supports queries for an athlete’s results at a given event;
    • (discipline_code, rank_no): supports leaderboards and “top three” queries;
    • (nationality, birth_date): supports statistics by country or age band;
    • updated_at: supports incremental sync and change awareness.

    7.3 Caching and invalidation

    • Athlete profiles: change infrequently, so a longer local cache TTL with active refresh works well;
    • Competition-period leaderboards: change frequently, so use a short TTL (e.g., 10–30 seconds) and actively invalidate by listening for “result changed” events;
    • Historical event leaderboards: essentially immutable, suitable for long-lived caching or even static output.

    Include a data version or definition identifier in the cache key, for example rank:{event_id}:{discipline}:{version}. The benefit: when a definition changes, you do not have to purge entries one by one — bumping the version number takes effect globally.

    8. API Design: Unified Definitions, Explicit Semantics

    Expose the following read-only API families, all of which state the data definition and the data-as-of timestamp:

    API Path (illustrative) Description
    Athlete profile /athletes/{id} Basic details plus version number
    Athlete results list /athletes/{id}/results Filterable by event, discipline, and year
    Athlete summary /athletes/{id}/summary Best rank, finish rate, number of starts
    Session results /sessions/{id}/results All valid results with statuses
    Event standings /events/{id}/standings Ranks grouped by discipline
    Result change history /results/{id}/revisions Revision details including reason and approval status

    Design points:

    1. Explicit definition parameters: for example rank_scope=personal|team, so callers know whether they are reading individual or team ranks;
    2. Stable pagination: use cursor-based pagination to avoid the performance degradation of deep offsets;
    3. Idempotent writes: internal write APIs use the business unique key (session + entrant + version) for idempotency, so redelivered messages never create duplicates;
    4. Change notification: result-change events carry the change type, affected scope, and effective time, so downstream systems can rebuild caches or recalculate points as needed.

    Sample response:

    1. {
    2.   "athlete_id": 100231,
    3.   "full_name": "Athlete Name",
    4.   "full_name_latin": "X Y",
    5.   "nationality": "FRA",
    6.   "birth_date": "1994-12-22",
    7.   "snapshot_version": 17,
    8.   "data_as_of": "2026-02-17T18:42:10Z",
    9.   "best_results": [
    10.     { "event": "International Multi-Sport Games", "discipline": "Men's Relay 4 x 7.5 km",
    11.       "scope": "team", "rank": 1, "team_time_ms": 4795200 }
    12.   ]
    13. }

    9. Observability and Data Quality Monitoring

    Quality problems on a results platform are usually not “the service is down” but “the data is wrong and nobody noticed.” Dedicated data quality monitoring is therefore required:

    • Completeness metrics: null rate on required fields, missing-rate of result records;
    • Consistency metrics: number of records where the parts do not match the total, number of rank gaps, number of abnormal team sizes;
    • Timeliness metrics: latency from collection to queryability, and revision activation latency;
    • Accuracy metrics: agreement rate from sampling comparisons against authoritative sources.

    Supporting measures:

    • Produce a daily inspection report that aggregates abnormal records by event;
    • Set threshold alerts on key metrics — for example, more than two valid results in the same session triggers an alert immediately;
    • Keep a trace of every change and support exporting audit logs by time range;
    • Label external APIs with data version and update time so a dispute can be pinned to a specific version quickly.

    10. Integration with Cloud Infrastructure

    A results platform faces clear traffic peaks during competition periods, and generic cloud capabilities help smooth capacity:

    • Object storage: holds original results-sheet files, timing exports, and video material as an evidence chain for data provenance;
    • Message queue: carries result-change events, asynchronously decoupling collection, validation, ingestion, and cache refresh;
    • Container platform: runs collection and validation jobs, scaling in and out during the competition period based on queue backlog;
    • Relational database and columnar storage: respectively serve transactional detail and large-scale analytical queries;
    • Log service and monitoring/alerting: centrally collect application logs and business metrics with unified alert rules;
    • Configuration center: manages each discipline’s rank rules, points mapping, and validation thresholds, so rule adjustments do not require a release.

    In Baidu AI Cloud, all of the above can be assembled from general-purpose services such as object storage, message service, container engine, database products, log service, and cloud monitoring. Specific specifications and configurations should be capacity-assessed against event scale, peak concurrency, and budget.

    11. Common Anti-Patterns and Improvements

    Anti-pattern Problem Improvement
    Overwriting results in place History lost, untraceable Revision table plus version number
    Result table keyed directly on athlete_id Cannot express relay events Introduce an entrant table
    Hard-coded discipline rules New formats require code changes Externalize rules; drive them with mapping tables
    Mixed time zones Dates misaligned across time zones Store in UTC uniformly; convert to the venue’s time zone at the presentation layer
    Inconsistent time precision Mixing seconds and milliseconds causes wrong ranks Store milliseconds as integers uniformly
    Tightly coupling splits to total time Handover times lost Dual-track storage: total time plus handover timestamps
    Running reports directly against detail tables Slow queries drag down the online database Aggregate layer plus precomputation plus cache

    12. Suggested Rollout Plan

    Phase 1: core modeling (1–2 weeks)

    Deliver the six core tables — event, discipline definition, session, entrant, athlete, and result — and establish the minimal end-to-end loop: results-sheet import → validation → ingestion → query API.

    Phase 2: revisions and timeline (1–2 weeks)

    Add the revision table, the valid-version view, the approval state machine, and cascade recalculation, ensuring every result change is traceable and rebuildable.

    Phase 3: relays and splits (1 week)

    Model and validate team rosters, legs, handover timestamps, and split results.

    Phase 4: analysis and external services (2–3 weeks)

    Build the aggregate layer, cache layer, statistics APIs, and data quality monitoring, and batch-backfill historical event data to verify the model’s compatibility with historical records.

    Suggested acceptance criteria:

    • Latency from result collection to queryability ≤ 60 seconds;
    • Agreement rate against sampled authoritative sources ≥ 99.9%;
    • Leaderboard API P99 response time ≤ 200 ms (cache-hit scenario);
    • Rebuilt historical leaderboards match the archived official results sheets exactly.

    13. Summary

    The value of competition results data lies not in how many fields it has, but in whether the definitions are unified and whether changes are traceable. The key takeaways are four:

    1. Model before you build: separating the “entrant” from the “athlete” is the prerequisite for supporting individual and team disciplines side by side;
    2. Facts may change; records must not be lost: carry every change with a revision table plus version number, so any historical leaderboard can be reconstructed precisely;
    3. Make the rules explicit: ranks, points, and presentation definitions are all configurable, keeping rules from being scattered across code;
    4. Make quality measurable: turn “is the data correct?” into an observable engineering problem using completeness, consistency, timeliness, and accuracy metrics.

    Once these foundations are solid, athlete profile presentation, live leaderboards, season analysis, and cross-edition comparison can all be built quickly on the same data asset.

    09040904

    Top articles