2.2. The Database

This section describes the database as used by JRaceman.

2.2.1. Relational Database

JRaceman uses a relational database (the Mckoi database) to store all of its data. A relational database contains a collection of tables. Each table has rows and columns. Each column has a name and a data type. Each row represents one data record. The intersection of a row and a column is a field, which is one value within one record.

Within each table, one or more columns are defined as primary key columns. The value of the primary key fields of each row in a table must be unique within that table. This is called the primary key constraint. All primary key fields must not be null. A database that maintains this constraint ensures entity integrity. In JRaceman, all of the tables use a single-column primary key called "id". Thus, within one table, all of the "id" values must be unique.

A table may also have columns with values that relate to the values in other tables (hence the name relational database). In the simplest case, a column of one table contains only values which are taken from the set of values in a column of another table. This column is then called a foreign key column. In JRaceman, many of the tables have foreign key columns pointing to the "id" column of another table. For example, the People table has a column called "teamId" which is a foreign key referring to the "id" column of the Teams table. The value in the "teamId" column of the People table must be one of the values from the "id" column of the Teams table; the database will not allow it to be any other value (other than blank). Having the database apply this constraint on allowable values (making sure that all of the non-blank foreign keys refer to a valid row in the referenced table) ensures referential integrity.

2.2.2. Schema

The collection of tables, with the column definitions and primary and foreign key constraints, is called a schema.

The schema in JRaceman consists of the following tables, each of which is documented in more detail with the editing tab for that table.

  • Areas - Defines the areas with a Site which are used for Events.

  • Challenges - Defines the Challenge category names.

  • Competitions - Defines the types of Events.

  • Complans, ComplanRules and ComplanStages - Defines complex progression plans available for a Meet.

  • ContextOptions - Options which are applied based on context-specific values for host, web context, or Meet.

  • Entries - Entries of People into Events, with information about Groups.

  • Events - Defines the Events to be contested at a Meet.

  • Exceptions - Defines the reasons why contestants can be disqualified from an Event.

  • Genders - Defines the names to use for gender-specific Events.

  • LaneOrder - Defines the lane ordering preference.

  • Lanes - Defines which Entries are in which Races, plus lane assignments, finishing times and positions, and other lane-specific data.

  • Levels - Defines the different levels of competition, typically age groups.

  • Meets - Defines the meets or regattas at which Events take place.

  • Options - Generic options.

  • People - Defines the people competing in Events.

  • Progressions - Defines the progressions that determine how competitors advance from one round of a race to the next.

  • Races - Defines the difference race that make up an Event.

  • RegistrationFees - Registration rates for Meets based on number of Events entered.

  • Registrations - Registrations of People for a Meet, with information about payment and signed waivers.

  • ScoringSystems and ScoringRules - Defines the point scoring systems available to use for a Meet.

  • SeedingPlans - Defines the seeding plans available for seeding the lane assignments when drawing lanes for an Event.

  • SeedingLists - Defines the seeding lists associated with seeding plans, allowing specifying a list of entrants to be seeded in an Event.

  • Simplans, SimplanRules and SimplanStages - Defines the simple progression plans available for a Meet.

  • Sites - Defines the sites at which Meets take place.

  • Stages - Defines the stages, such as Heat or Final, for the difference rounds of an Event.

  • Teams - Defines the Teams of People competing in Events.

2.2.3. ID Fields

In JRaceman, every table has a single-column primary key called "id". As with any key in a relational database, the value of this field must be unique among all rows within the table.

When creating a new record in any editing tab, the ID field is editable, allowing you to fill in a value if you desire. When editing an existing record, however, the ID field is not editable.

Normally you do not need to enter a value for the ID in a new record; the program automatically generates one for you based on the type of record, and sometimes based on the data in other fields of that record.

In most cases, the tab supplies an ID prefix, which is a string such as "ID". The program takes this ID prefix and adds a number to it to create a unique ID value such as "ID1". As each new record is created, the next number is used with the ID prefix, generating a sequence such as "ID1", "ID2", "ID3", etc. If a record is deleted, that ID will be reused later.

Some tabs use a different technique to generate a unique ID, as documented for those tabs. In these cases, the program may silently modify that unique ID by adding a numeric suffix or by changing the numeric suffix so that the ID string is unique.

If you choose to enter an ID value for a new record, the program will check to see if your ID value is unique when you save the record. If it is not, the program will automatically add a numeric suffix, or modify an existing numeric suffix, if given, to produce a unique ID. In this case, you will be asked to accept this new ID before the record is saved.