Add Pace Zones

[edance]

Pace Zones Implementation Options

User Stories

• As a marathon runner, I'd like to see the time I've spent running in each training zone for my training block (18 weeks).
• As a marathon runner, I might be training for multiple races at once (like a half marathon before my marathon) with different paces. I'd like to see a pace breakdown for each race.

Option 1: Precalculate Summaries

This option minimizes PostgreSQL calculation overhead by precalculating summaries for each race goal.

Database Schema

1. race_goals (existing table)

CREATE TABLE race_goals (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  race_date DATE NOT NULL,
  target_time INTERVAL,
  distance NUMERIC(8, 2), -- in meters
  name VARCHAR(255)
);

2. pace_zones

CREATE TABLE pace_zones (
  id SERIAL PRIMARY KEY,
  race_goal_id INTEGER NOT NULL,
  zone_number INTEGER NOT NULL,
  zone_name VARCHAR(50),
  min_pace INTERVAL,
  max_pace INTERVAL,
  FOREIGN KEY (race_goal_id) REFERENCES race_goals(id) ON DELETE CASCADE,
  UNIQUE (race_goal_id, zone_number)
);

3. activity_pace_zone_summary

CREATE TABLE activity_pace_zone_summary (
  id SERIAL PRIMARY KEY,
  activity_id INTEGER NOT NULL,
  pace_zone_id INTEGER NOT NULL,
  time_in_zone INTERVAL,
  distance_in_zone NUMERIC(8, 2),
  FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
  FOREIGN KEY (pace_zone_id) REFERENCES pace_zones(id) ON DELETE CASCADE,
  UNIQUE (activity_id, pace_zone_id)
);

Implementation

1. When a user sets up a new race goal:

   • Insert a record into race_goals
   • Insert multiple records into pace_zones, one for each zone (typically 5-6 zones)

2. When a new activity is uploaded:

   • Calculate the time and distance spent in each zone based on the trackpoints and the user's current race goal(s)
   • Insert records into activity_pace_zone_summary for each relevant zone

Pros

• Efficient querying of pace zone data
• Quick retrieval of zone data without recalculation
• Supports multiple race goals and custom zone definitions

Cons

• Requires storage of precalculated summaries
• Less flexible if zone definitions change frequently

Option 2: Trackpoint Segments

This option stores the difference between each trackpoint in a separate table, allowing PostgreSQL to perform real-time calculations for time spent in each pace zone.

Database Schema

1. trackpoint_segments

CREATE TABLE trackpoint_segments (
  id SERIAL PRIMARY KEY,
  activity_id INTEGER NOT NULL,
  segment_start TIMESTAMP WITH TIME ZONE NOT NULL,
  segment_duration INTERVAL NOT NULL,
  segment_distance NUMERIC(8, 4) NOT NULL,
  segment_pace INTERVAL NOT NULL,
  FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE
);

2. pace_zones (same as Option 1)

Implementation Considerations

1. Indexing: Create indexes on activity_id, segment_start, and segment_pace for performance.
2. Partitioning: For large datasets, consider partitioning the trackpoint_segments table by activity_id or date ranges.
3. Materialized Views: Use materialized views for common queries to improve performance.
4. Query Optimization: Carefully optimize SQL queries for zone calculations.

Pros

1. Flexibility: Zones can be redefined without recalculating summaries.
2. Accuracy: Calculations are always based on the most granular data available.
3. Storage efficiency: Doesn't require storing precalculated summaries.
4. Adaptability: Easier to implement new types of analyses or zone definitions.

Cons

1. Performance: Queries may be slower, especially for large datasets or complex analyses.
2. Complexity: SQL queries for zone calculations can become complex.
3. Scalability concerns: As the dataset grows, query performance may degrade.

Conclusion

Both options have their merits and drawbacks. The choice between them depends on factors such as:

• Expected data volume
• Frequency of zone definition changes
• Performance requirements
• Flexibility needs for future feature additions

Consider implementing a hybrid approach or starting with one option and being prepared to transition if needed as your application scales.