ctst-dev

Create Database Tables

Database Schema

Database Schema Diagram

Data Tables

The Master database tables are currently stored in individual CSV files, one for each table, derived from Excel data tables generated using Power Query.

Currently, the “database” (CSV table files) are used “as-is”, to generate a static web site (see the generate_website directory), which can easily be re-generated and re-deployed whenever the data is updated. The CSV files could also be used to set up a database in an actual database management system.

The tables should all be normalized with no data duplication among any tables.

All tables have a numeric primary key ID. Most start at 1, with exceptions such as Competitor ID and Division ID.

It should be obvious what datatype is used for each field: integer, text, or date.

table_Competitors

Contains the CTST-assigned Competitor ID for each competitor, along with their name. Used only in table_Results.

Note that in some cases there may be Competitors that have had multiple Competitor IDs assigned for multiple variations of their name. Work has been done to eliminate duplicate Competitor entries. However, there may still be instances of Competitors with multiple IDs for different variations of their name. These may be addressed in the future if such cases come to light.

table_Contests

A Contest is a combination of an Event (e.g., 2019 Colorado Country Classic), in a given Division (Novice, Intermediate, etc.), for a given Role (Leader or Follower). Leader contests and Follower contests must be tracked separately since the points awarded to Leaders and Followers are based on the Contest Tier, which may be different for Leaders and Followers, and is determined by the total number of contestants that competed in that Division (in ALL heats) as a Leader or as a Follower.

table_Divisions

Note that because there have not yet been any “All-Stars” contests held so far, there may not yet be a table entry for “All-Stars”.

table_EventLocations

For consistency in reporting and use as a data entry lookup-table, each location in which an Event was held has its own table entry.

table_EventNames

For consistency in reporting and use as a data entry lookup-table, each named event has its own table entry. This is separate from table_EventLocations to allow events such as World’s to be grouped without regard to the fact that the event was held in multiple locations.

table_Events

Each time an event is held there will be a separate Event entry for the Event on that date. If desired, other fields could be added to this table in the future, e.g., Event organizer name, contact info, web site, etc.

table_Results

The lowest level of detail in the Points database is the Result record. A Result record is required for each placement in the Final heat of any Contest in which points are awarded. A Result entry is not required if no points were awarded. However, there is nothing to preclude creating a Result entry for all Competitors in the Final heat, or for Contests in the non-pointed divisions (e.g., Masters), as long as the Competitor is assigned a Competitor ID. The Points value assigned to a Result is determined by the data entry function, and is not automatically derived from the Result/Tier. This allows future changes to the points/tier levels without affecting existing points awarded.

table_Roles

In the legacy Points Registry, each competitor was listed in EITHER the Men’s OR the Women’s Points Registry. The new database design is more flexible, and allows a Role to be used instead (L/F, Leader/Follower, Lead/Follow, etc.) Competitors can accrue points in either or both roles, as allowed by the rules in effect for each Event.

Database Creation

create_db_tables.xlsx is an Excel workbook that uses Power Query to generate the database tables.

This Excel workbook uses as its data source the CSV file, points_latest.csv, in the create_csv_new directory, a combined file containing all Leader and Follower points data, for both old and new contest data.

To (re-)generate the Excel data tables for the database in the Excel workbook, update the Data Source, then re-run the Power Query queries:

The actual CSV files for the data tables are not written out automatically when Power Query is run. Each CSV file must be manually saved from its corresponding table/worksheet in the Excel workbook.

Open create_db_tables.xlsx in Excel, then for each of the table_ worksheets (except for base_data):

To view the actual Power Query queries used to generate the data tables, update the Data Source as described above, then:

This site is open source. Improve this page.