| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

View
 

COP4834 Project Specifications - Race Manager

Page history last edited by Dr. Ron Eaglin 6 years, 9 months ago

COP4834 Project Specifications - Race Manager

 

 


 

Introduction

 

The race manager is a software system that is designed to assist with the registration and scoring of adventure races. A database design at the end of this document had been created, the system may also use the XMLPropertyObject ( Getting Started - Using the XmlPropertyObject for COP4834 )  to manage the data for this project.

 

Defining a Race

 

The system must allow an administrator to set up a race. There are 2 parts to the setup of a race; the registration requirements, and the race scoring requirements. Both of these are discussed here. A preliminary design of a table system for defining races and registrations is here

 

/* Entry for Every Racer on a Team */
CREATE TABLE Racers (
 id INT PRIMARY KEY IDENTITY(1,1),
 FirstName NVARCHAR(100),
 LastName NVARCHAR(100), 
 Email NVARCHAR(250),
 Phone NVARCHAR(100),
 Address NVARCHAR(200)
)

/* Simply tracks a team name */
CREATE TABLE Teams (
 id INT PRIMARY KEY IDENTITY(1,1),
 Name NVARCHAR(200)
)

/* Keeps track of all racers who have ever raced on a team */
CREATE TABLE TeamRacers (
  id INT PRIMARY KEY IDENTITY(1,1),
  RacerId INT FOREIGN KEY REFERENCES Racers(id),
  TeamId  INT FOREIGN KEY REFERENCES Teams(id)
)

/* Keeps track of all races */
CREATE TABLE Races (
  id INT PRIMARY KEY IDENTITY(1,1),
  Name NVARCHAR(200),
  RaceType NVARCHAR(20),
  P4EarlyRegistrationFee numeric(4,2),
  P4RegularRegistrationFee numeric(4,2),
  P4LateRegistrationFee numeric(4,2), 
  P3EarlyRegistrationFee numeric(4,2),
  P3RegularRegistrationFee numeric(4,2),
  P3LateRegistrationFee numeric(4,2), 
  P2EarlyRegistrationFee numeric(4,2),
  P2RegularRegistrationFee numeric(4,2),
  P2LateRegistrationFee numeric(4,2), 
  SoloPEarlyRegistrationFee numeric(4,2),
  SoloPRegularRegistrationFee numeric(4,2),
  SoloPLateRegistrationFee numeric(4,2),
  EarlyRegistrationCutoff DateTime, 
  RegularRegistrationCutoff DateTime,
  LateRegistrationCutoff DateTime,
  CheckinTime NVARCHAR(50),
  StartTime NVARCHAR(50),
  FinishTime NVARCHAR(50),
  Notes NVARCHAR(500)
)


/* Created when a team registers for an event */
CREATE TABLE TeamRegistration (
  id INT PRIMARY KEY IDENTITY(1,1),
  TeamId INT FOREIGN KEY REFERENCES Teams(id),
  RaceId  INT FOREIGN KEY REFERENCES Races(id),
  Division NVARCHAR(50),
  ReferredBy NVARCHAR(200),
  HeardAboutResponse NVARCHAR(200),
  TeamPictureUrl NVARCHAR(500),
  Notes NVARCHAR(500)
)

/* Keeps track of all racers who registered for a team on an event */
CREATE TABLE TeamRegistrationRacers (
  id INT PRIMARY KEY IDENTITY(1,1),
  RacerId  INT FOREIGN KEY REFERENCES Racer(id),
  TeamRegistrationId INT FOREIGN KEY REFERENCES TeamRegistration(id),
  RacerPictureUrl NVARCHAR(500),
  Notes NVARCHAR(250)
)


/* Tracks all payments for a specific race  */

CREATE TABLE TeamRegistrationPayments (
  id INT PRIMARY KEY IDENTITY(1,1),
  TeamRegistrationId INT FOREIGN KEY REFERENCES TeamRegistration(id),
  PaymentMadeBy INT FOREIGN KEY REFERENCES Racers(id),
  Amount Numeric(4,2),
  Type NVARCHAR(10),  /* Dollars, Credits */
  Notes NVARCHAR(250)

)

/* The following 2 tables are used for race promotions and listing of events */

/* Race Listing Locations keeps track of all places we list courses for advertising */
CREATE TABLE RaceListingLocation (
  id INT PRIMARY KEY IDENTITY(1,1),
  Url NVARCHAR(250),
  Region NVARCHAR(100), 
  Notes NVARCHAR(500)
)

/* Created when a Race is listed for advertising */
CREATE TABLE RaceListing (
  id INT PRIMARY KEY IDENTITY(1,1),
  Race INT FOREIGN KEY REFERENCES  Races(id),
  RaceListingLocation INT FOREIGN KEY REFERENCES  RaceListingLocation(id), 
  Racer INT FOREIGN KEY REFERENCES  Racers(id), /* Person who listed race */
  Notes NVARCHAR(500) 
)


)

 

 

Registration Requirements for Races

 

A typical race has multiple potential registrations based on 2 parameters; size of team - which is 1-4 persons, and early, regular, or late registration. A registration page showing the registration options would look something like this. (These are samples)

 

 

Team Information for Registration

 

And clicking any one of the options would take the user to a page where they would actually perform the entry of the information

 

 

Racer Information for Registration

 

And of course have the ability to enter information for the team-mates - and also pay.

 

 

The system must collect the following registration information for a given race;

 

  1. Race registration cost
  2. Name of team
  3. Team category - these are defined for each race
  4. Referral Name
  5. For each team member
    1. First Name
    2. Last Name
    3. Gender
    4. T-Shirt Size
    5. Age
    6. Address
    7. Phone

 

Scoring Requirements for Races

 

Basics

 

An Adventure race is made up of multiple race sections. Each section is typically performed on foot, bike or boat - but sections can contain multiple elements also. Each section will have checkpoints which the teams must visit, and the team is scored by how many checkpoints they visit.  An example of a team scoring is shown below.

 

 

Database Design

 

From the leaderboard you can see what checkpoints a team visited and how long each section of the race took. A database design for managing the race checkpoints and the leaderboard is shown below. It relies on tables that are in the registration database above.

 

/* The following tables define the segments of a race for scoring */
/* Race Segment is an individual segment of a race */
CREATE TABLE RaceSegment (
  id INT PRIMARY KEY IDENTITY(1,1),
  Race INT FOREIGN KEY REFERENCES  Races(id),
  Discipline NVARCHAR(50),
  Title NVARCHAR(200), 
  MapUrl NVARCHAR(500),
  Comments NVARCHAR(500)
)
/* Control Points Make up a RaceSegment  */
CREATE TABLE ControlPoint (
  id INT PRIMARY KEY IDENTITY(1,1),
  RaceSegmentId INT FOREIGN KEY REFERENCES  RaceSegment(id),
  Value INT,
  ControlPointNumber NVARCHAR(5),  /* Number on flag */
  ControlPointRaceId NVARCHAR(10),  /* Id in race */
  ControlPointLatitude NVARCHAR(30),
  ControlPointLongitude NVARCHAR(30)
 )
/* Section deals with scoring of teams in races */
CREATE TABLE TeamScore (
  id INT PRIMARY KEY IDENTITY(1,1),
  RaceId  INT FOREIGN KEY REFERENCES  Races(id),
  TeamId  INT FOREIGN KEY REFERENCES  Teams(id),
  TotalScore INT,
  Notes NVARCHAR(500)
)
CREATE TABLE TeamSegmentScore (
  id INT PRIMARY KEY IDENTITY(1,1),
  TeamScoreId INT FOREIGN KEY REFERENCES  TeamScore(id),
  ControlPointIds NVARCHAR(500),     /* Easiest to simply list and delimit */
  TotalScore INT,
  TimeStart DATETIME,
  TimeComplete DATETIME,
  Notes NVARCHAR(500)
)
 

 

 

 

 

Comments (0)

You don't have permission to comment on this page.