Building a Formula One Race Tracker – Part One

csharp Microsoft SQL Server

Contents

  1. Part One – Project Overview
  2. Part Two – Database Schema

A little while ago I answered a question over on StackOverflow from someone who was asking how they could design a database that would allow them to track individual races in a Formula One season, this question formed the foundation for this series of posts which will hopefully demonstrate my building process of a small simplistic web application, using a lot of what I have learned over the last five years.  

What do we need?

As we are going to be building a C# web application, there are a few things we will need before we get started;

Note: This project is available in full over on Git Hub

Microsoft SQL Server

I am going to need a version of SQL Server, for this project I am going to be using SQL Server 2017 development edition you can grab the development edition of SQL Server from the Visual Studio downloads page for learning and personal development. 

Visual Studio

This project will be written in C# in Visual Studio 2017 I am going to be using the community version which again is available from the Visual Studio downloads page 

The Requirements

Before I start to write any code I need to understand what the goals are for the project, not only so that I have a clear understanding of where the project will go and it’s function but also so that I don’t stray off track and start writing additional features that could be implemented in future releases and avoid scope creep in the current release.

The Database

  • A Simple web application that will allow for
    • Inputting of race results and recording results against individual drivers
    • Viewing an overall table of race results for a given season
  • The ability to record each race split across an entire weekend.
    • Practice Sessions
    • Qualification
    • Race Day
  • The ability for the application to calculate the points for a driver based on their finishing position.
    • 1 (25)
    • 2 (18)
    • 3 (15)
    • 4 (12)
    • 5 (10)
    • 6 (8)
    • 7 (6)
    • 8 (4)
    • 9 (2)
    • 10 (1)
    • FL (1)
  • The ability to track drivers and the team in which they are driving for between seasons.
    • Drivers will have a start and end date for each team they race for.
    • A driver with an end date populated for a team will indicate that the driver no longer drives for that team.
  • The ability to track which engines teams are using between seasons.
    • Engines much like drivers will have a start and end date for each team.
    • A populated end date will mean the engine is no longer in use for this team.
  • The ability to track race circuits and include them or not include them for an individual season.
  • The Web Application

  • Lightweight and fast loading
  • Easy to use on both desktop & mobile
  • Responsive
  • The Design

    To start I always like to draw out what the database will look like on paper, for me it gives a good starting point of what tables the database will be made up of, which tables will need to talk to one another, which columns will become primary keys, if we will have any default values and the data types of the columns.   

    The Database Design

    The first sketch that I did was a mapping of the tables, It didn’t include any of the data types, what I decided to do was to map out the columns on paper, transfer that into Google Sheets and map the data types to the columns there.

    Once the initial drawing of the table structure was done I completed the mapping of the table data types below are all of the final data types and mappings that I came up with in Google Sheets, it outlines everything that I will need for Part Two, which is where the actual schema will be written.

    Driver Table
    Column Name Data Type Data Length PK FK Identity Default
    Driver_ID TINYINT Yes No No
    Forename VARCHAR 20 No No No
    Surname VARCHAR 20 No No No
    Nationality VARCHAR 20 No No No
    Retired BIT No No No
    Driver_Team
    Column Name Data Type Data Length PK FK Identity Default
    Team_ID TINYINT Yes No
    Driver_ID TINYINT Yes No
    Start_Date DATETIME No No
    End_Date DATETIME No No
    Teams
    Column Name Data Type Data Length PK FK Identity Default
    Team_ID TINYINT Yes No No
    Team_Name VARCHAR 20 No No No
    Active BIT No No No 1
    Team_Engine
    Column Name Data Type Data Length PK FK Identity Default
    Team_ID TINYINT No Yes No
    Engine VARCHAR 15 No No No
    Start_Date DATETIME No No No
    End_Date DATETIME No No No
    Circuit
    Column Name Data Type Data Length PK FK Identity Default
    Circuit_ID TINYINT Yes No No
    Circuit_Name NVARCHAR 60 No No No
    Circuit_Type VARCHAR 15 No No No
    Direction VARCHAR 50 No No No
    Circuit_Location NVARCHAR 50 No No No
    Last_Length_Used DECIMAL 5,3 No No No
    Grand_Prix_Name NVARCHAR 70 No No No
    Start_Date DATETIME No No No
    End_Date DATETIME No No No
    Race Types
    Column Name Data Type Data Length PK FK Identity Default
    Race_Type_ID TINYINT Yes No No
    Race_Type NVARCHAR 10 No No No
    Race
    Column Name Data Type Data Length PK FK Identity Default
    Race_ID TINYINT Yes No Yes
    Race_Date DATETIME No No No
    Driver_ID TINYINT No Yes No
    Circuit_ID TINYINT No Yes No
    Final_Position TINYINT No No No
    Points TINYINT No No No
    Race_Type TINYINT No No No

     

    The Application Design

    I sketched out a very rough drawing of what I wanted the web application to look like, as you can see it is going to be very simple, made up of just two pages, a submissions page and a results page which can be accessed from either of the pages, each page will have two buttons, one at the top of the page and one at the bottom, the submission page will be made up of a web form

    As outlined in our requirements the application needs to be simple, responsive and fast loading, with that in mind we are going to make use of Bootstrap, specifically version 4.

    Using the above rough sketch I set about writing the HTML for the pages, why? Once the design of the application is done I like to develop out the idea in pure HTML/CSS so I can see how the application will look, when it comes to adding in the functionality the existing HTML that was developed at this stage can be used simply adding the page functionality.

    This is what the pages will look like;