Building a Formula One Race Tracker – Part One

Posted On: In: C# 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

The Web Application

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.

Rough sketch of the database design

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 0
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

Rough sketch of the application design

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;

Initial design of the race results submission page

Initial Design Of The Race Results Page