10.2 Designing Plans for Data Collection, Storage, Analysis, and AMS Workflows

10.2 Designing Plans for Data Collection, Storage, Analysis, and AMS Workflows

As high-performance departments scale across multiple squads, seasons, and high-frequency measurement technologies, ad-hoc data management rapidly collapses. Designing a resilient architecture for data collection, storage, and analysis requires transitioning from fragmented desktop spreadsheets to relational database schemas, automated application programming interface ($\text{API}$) pipelines, rigorous metadata governance, and enterprise backup infrastructure.

---

Why Flat Spreadsheets Fail at Scale

In early-stage sport science programs, practitioners frequently store testing and workload data in disconnected spreadsheet workbooks. While spreadsheets are useful for rapid ad-hoc scratchpad calculations, relying on flat files as a primary database introduces severe structural failure modes:

• Entity Resolution and Naming Inconsistencies: Manual typing across tabs produces mismatched string identifiers ("John Smith" in the $\text{GNSS}$ tab, "Smith, John" in the force-plate tab, and "J. Smith" in the wellness sheet), breaking automated table joins (VLOOKUP/XLOOKUP) and orphaning athlete records.
• Silent Formula Corruption and Lack of Audit Trails: A single accidental drag-and-drop or sorting error can shift rows relative to athlete names or overwrite a rolling average formula with a static number without generating any warning or historical log.
• Concurrency Bottlenecks and Row Limits: High-frequency time-series streams (such as $10\text{ Hz}$ $\text{GNSS}$ or $1{,}000\text{ Hz}$ dual force-plate traces) generate millions of rows per week, rapidly exceeding spreadsheet memory limits and causing file corruption when multiple practitioners attempt simultaneous edits.

---

Relational Database Design ($\text{SQL}$) and Tidy Data Architecture

Primary Keys ($\text{PK}$), Foreign Keys ($\text{FK}$), and Star Schemas

A production Athlete Management System ($\text{AMS}$) is built upon a Relational Database Management System ($\text{RDBMS}$) queried via Structured Query Language ($\text{SQL}$). Rather than duplicating athlete demographic details inside every single testing sheet, a relational database organizes information into interconnected tables structured around a Star Schema:

• Dimension Tables (Context & Entities): Store relatively static descriptive attributes about entities. For example, the athletes table stores athlete_id, first_name, last_name, date_of_birth, and dominant_limb; the calendar_microcycle table stores session_date, season_phase, and days_to_match ($\text{MD}-3$, $\text{MD}+1$); and the protocols table defines protocol_id, hardware_vendor, and sampling_rate_hz.
• Fact Tables (Longitudinal Event Streams): Store time-stamped quantitative measurements and testing events, such as gps_session_summary, cmj_trials, daily_wellness_log, and isokinetic_tests.
• Primary Keys ($\text{PK}$): Every table possesses a Primary Key—a unique, non-null, immutable identifier for each individual row (e.g., athlete_id = "ATH_0089" in the athletes dimension table, or trial_uuid in the cmj_trials fact table). Because athlete_id never changes even if an athlete changes their jersey number, marries and changes their surname, or transfers between developmental and first-team rosters, longitudinal continuity is guaranteed.
• Foreign Keys ($\text{FK}$): A column in a fact table that references the Primary Key of a dimension table (e.g., athlete_id inside gps_session_summary referencing athletes.athlete_id). The database engine enforces referential integrity, preventing a practitioner or script from inserting a workload record for an athlete_id that does not exist in the master athletes table. Furthermore, creating composite B-tree indexes on (athlete_id, session_date) enables the database engine to execute multi-season longitudinal joins in milliseconds rather than scanning millions of unindexed rows.

Tidy Data Principles (Third Normal Form / $\text{3NF}$)

To ensure seamless querying, statistical modeling in $\text{R}$ or Python, and business intelligence ($\text{BI}$) visualization, database tables must adhere to Hadley Wickham's Tidy Data Principles (which mirror Codd's Third Normal Form [$\text{3NF}$] in relational algebra):

1. Every variable forms a single column (e.g., jump_height_cm, peak_propulsive_force_n, braking_impulse_ns).
2. Every observation forms a single row (e.g., one specific athlete's single jump trial on a specific timestamp).
3. Every type of observational unit forms a separate table (keeping daily $\text{GNSS}$ session summaries in a separate table from multi-trial force-plate jumps rather than mashing disparate granularities into one wide sheet).

---

Automated Data Pipelines ($\text{ETL}$ / $\text{ELT}$) and API Integration

Manual CSV downloading and uploading wastes practitioner hours and introduces human error. High-functioning sport science departments construct automated Extract, Transform, Load ($\text{ETL}$) (or cloud-native Extract, Load, Transform [$\text{ELT}$]) pipelines orchestrated via scheduled cloud scripts:

1. Extract (Ingestion Layer — Webhooks vs. Scheduled Polling): Automated scripts communicate with third-party hardware clouds (e.g., $\text{GNSS}$ tracking servers, force-plate cloud databases, nocturnal $\text{HRV}$ platforms) via RESTful $\text{APIs}$ using bearer token or $\text{OAuth 2.0}$ authentication to retrieve structured JSON (JavaScript Object Notation) payloads. Ingestion can be triggered either via event-driven webhooks (where the force-plate cloud pushes a JSON payload to the team server the instant a jump trial is completed) or scheduled cron polling (e.g., querying the $\text{GNSS}$ API every $15\text{ minutes}$ while respecting API rate limits and pagination cursors).
2. Transform (Staging & Validation Layer): Before any external data touches the production warehouse, it passes through an automated staging gate that executes:
   • Schema & Type Validation: Verifying that required fields exist and numeric metrics are floats/integers rather than corrupted text strings.
   • Unit Standardization to $\text{SI}$ Units: Converting all incoming measurements into standardized International System of Units ($\text{SI}$)—meters ($\text{m}$), seconds ($\text{s}$), kilograms ($\text{kg}$), Newtons ($\text{N}$), and meters per second ($\text{m}\cdot\text{s}^{-1}$). If a secondary weight room exports body mass in pounds ($\text{lb}$) while the primary lab exports in kilograms ($\text{kg}$), failing to convert in the staging layer will deflate relative peak power ($\text{W}\cdot\text{kg}^{-1}$) by a factor of $2.2046$, because the body-mass denominator is $2.2046$ times too large.
   • Timestamp Normalization ($\text{ISO 8601}$): Standardizing all dates and times to the $\text{ISO 8601}$ international standard (YYYY-MM-DDTHH:MM:SSZ with explicit $\text{UTC}$ offset). This eliminates ambiguity between American (MM/DD/YYYY) and European (DD/MM/YYYY) date formats and accurately synchronizes biological sleep/recovery data when teams travel across multiple time zones.
   • Idempotency & Deduplication: Ensuring that re-running an API sync script for the same date updates existing records (UPSERT on unique trial IDs) rather than inserting duplicate rows.
3. Load (Warehouse & Presentation Layer): Validated, cleaned records are committed via atomic $\text{SQL}$ transactions into the production relational database, automatically refreshing downstream analytical models and coach dashboards.

---

Data Dictionary Governance and the 3-2-1 Backup Strategy

The Canonical Data Dictionary

Every sport science database must be accompanied by a living, version-controlled Data Dictionary (metadata registry). For every variable in the $\text{AMS}$, the Data Dictionary explicitly specifies:

• Canonical Column Name & Display Label: e.g., hsr_distance_m ("High-Speed Running Distance $> 5.5\text{ m}\cdot\text{s}^{-1}$").
• Physical Unit & Precision: e.g., meters ($\text{m}$), rounded to $1$ decimal place.
• Exact Mathematical Formula & Threshold Definition: Documenting whether High-Speed Running is defined as an absolute threshold ($> 5.5\text{ m}\cdot\text{s}^{-1}$ for a minimum dwell time of $0.5\text{ s}$) or an individualized threshold ($> 75\%$ of each athlete's peak velocity). If a vendor firmware update changes the dwell-time filter from $0.5\text{ s}$ to $1.0\text{ s}$, the Data Dictionary logs that metadata version change so longitudinal comparisons are adjusted.
• Missing-Data Representation (NULL vs. 0): A foundational rule of database governance is never encoding a missing observation as 0 or an arbitrary numeric placeholder such as -999. In a relational database, a missed test must be stored as a true database NULL (or NA in $\text{R}$/NaN in Python). If a missed countermovement jump or missed $\text{GNSS}$ session is recorded as 0, SQL AVG() and STDDEV() functions will treat 0 as a valid physiological performance of zero meters, catastrophically depressing the athlete's rolling 28-day chronic workload and distorting team $Z$-scores.

The 3-2-1 Institutional Backup Strategy

To ensure long-term Availability and disaster recovery against hardware failure, accidental table drops, or ransomware attacks, sport science infrastructure must implement the 3-2-1 Backup Rule:

• 3 Copies of Data: Maintain at least three complete copies of all institutional data (1 primary live production database plus 2 independent backup copies).
• 2 Different Storage Media: Store the copies across at least two distinct storage technologies or media types (e.g., cloud relational database storage plus local encrypted Network-Attached Storage [$\text{NAS}$] or independent cloud object storage buckets).
• 1 Off-Site & Immutable Copy: Keep at least one backup copy geographically off-site in an immutable (Write-Once-Read-Many / $\text{WORM}$) or air-gapped vault that cannot be encrypted or deleted even if primary administrator credentials are compromised.