Rowingstats API v88

As of build version 88

The rowing stats website is built upon a postgresql database. This article outlines the schema.

NB: Every table listed below also includes a primary key, or pk, field. These are not listed below to avoid duplication.

Rower, Club and Race data

Competition

Purpose: defines the highest level race. Examples include the Olympic Games, the Head of the Charles Regatta, BUCS Championships.

Fields:

• name: the name of the competition (CharField)

Event

Purpose: defines the events within a competition - eg M1x, Temple Challenge Cup, Intermediate Eights.

Fields:

• name: the name of the event (CharField)
• comp: the related competition (ForeignKey)
• type: the type of the event, used to distinguish events for score purposes (CharField). Can only be one of several types: Sweep, Sculling, Lwt Sweep, Lwt Sculling, Para-Sweep, Para-Sculling.
• distance: the distance of the event (CharField). Defaults to 2000m. At some point in the future this will be converted to an integer field.

Race

Purpose: defines an instance of a particular event, i.e. an individual race. e.g the A Final of the W4- at the Olympics.

Fields:

• name: the name of the race (CharField)
• date: the date of the race (DateField)
• raceclass: the class of the race. Optional. (CharField). This doesn't have much functionality at present but is used to distinguish international level events for calculating score bonuses.
• order: the order of the race (PositiveSmallIntegerField). This is used to distinguish races that happen on the same day (i.e. date is identical). It can take one of three values: 0 - Heat; 1- Semifinal; 2 - Final.
• complete: used to determine whether the race entry is complete. Used for internal purposes for masking dummy or unfinished race entries. If marked as zero/false, the race is ignored. (BooleanField)

Rower

Purpose: defines an individual athlete. NB this also includes coxes.

Fields:

• name: the name of the rower (CharField)
• gender: the gender of the rower (CharField). Can be one of three choices: M, W, U.
• nationality: the nationality of the rower (CharField). Usually expressed as the country code e.g. GBR
• iscox: indicates whether this person is primarily (but not exclusively) a cox. (BooleanField)

Club

Purpose: defines a club for which a rower may be a member.

Fields:

• name: the name of the Club (CharField)
• countrycode: where a Club is a national club, the country code field is used to provide the country code when international results are being added. E.g. GB Rowing may map to GBR (CharField)

Result

Purpose: defines the individual results within a race.

Fields:

• race: the related race for that result (ForeignKey)
• position: the position of the result, eg 1 for first (IntegerField)
• crew: the rowers who were part of the crew in that result (ManyToManyField)
• club: the club(s) that were associated with the result (ManyToManyField). Is M2M in case the crew is a composite.
• flag: Used to indicate whether the crew is the A crew, GBR1, Crew IV or whatever. (CharField)
• cox: the cox associated with that crew. Optional. (CharField)

Time

Purpose: stores times associated with a result. Reflecting the varying nature of times, these are stored as a foreignkey relation to the result.

Fields:

• result: the associated result (ForeignKey)
• description: a label for the time. E.g. Finish, 500m, halfway, Barrier. (CharField)
• value: the time recorded (TimeField)
• order: the order in which the times should be displayed. E.g. 500m (0), 1000m (1), 1500m (2), Finish (3) (PostiveSmallIntegerField)

Ranking data

Score

Purpose: provides the estimate of a rower's skill at a given point in time for a given result.

Fields:

• mu: the estimate of the skill level (FloatField)
• sigma: the standard deviation/variance of the estimate (I can't remember which). (FloatField)
• rower: the associated rower (ForeignKey)
• result: the associated result (ForeignKey)

ScoreRanking

Purpose: for the purposes of the ranking system, score data is precomputed separately and stored distinct from the main relational data. In other words, a flattened table is calculated just for the rankings.

Fields:

• mu: the estimate of the skill level (FloatField)
• sigma: the standard deviation/variance of the estimate (again I can't remember which). (FloatField)
• delta_mu_sigma: mu minus sigma. (FloatField)
• rower: the associated rower (ForeignKey)
• date: the date of the ranking (DateField)
• type: the type of the event, used to distinguish scores of different event types (CharField). Can only be one of several types: Sweep, Sculling, Lwt Sweep, Lwt Sculling, Para-Sweep, Para-Sculling.
• sr_type: the type of the ranking (CharField). Can only be one of two types: Current, or All time.