Contents
- Project Overview
- 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 loadingEasy to use on both desktop & mobileResponsiveThe 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 | <td data-sheets-value="{"1":2,"2":"Data Type"}">
Data Type
</td>
<td data-sheets-value="{"1":2,"2":"Data Length"}">
Data Length
</td>
<td data-sheets-value="{"1":2,"2":"PK"}">
PK
</td>
<td data-sheets-value="{"1":2,"2":"FK"}">
FK
</td>
<td data-sheets-value="{"1":2,"2":"Identity"}">
Identity
</td>
<td data-sheets-value="{"1":2,"2":"Default"}">
Default
</td>
| Driver_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Forename | <td data-sheets-value="{"1":2,"2":"VARCHAR"}">
VARCHAR
</td>
<td data-sheets-value="{"1":3,"3":20}">
20
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Surname | <td data-sheets-value="{"1":2,"2":"VARCHAR"}">
VARCHAR
</td>
<td data-sheets-value="{"1":3,"3":20}">
20
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Nationality | <td data-sheets-value="{"1":2,"2":"VARCHAR"}">
VARCHAR
</td>
<td data-sheets-value="{"1":3,"3":20}">
20
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Retired | <td data-sheets-value="{"1":2,"2":"BIT"}">
BIT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":3,"3":0}">
</td>
| Driver_Team |
| Column Name | <td data-sheets-value="{"1":2,"2":"Data Type"}">
Data Type
</td>
<td data-sheets-value="{"1":2,"2":"Data Length"}">
Data Length
</td>
<td data-sheets-value="{"1":2,"2":"PK"}">
PK
</td>
<td data-sheets-value="{"1":2,"2":"FK"}">
FK
</td>
<td data-sheets-value="{"1":2,"2":"Identity"}">
Identity
</td>
<td data-sheets-value="{"1":2,"2":"Default"}">
Default
</td>
| Team_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Driver_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Start_Date | <td data-sheets-value="{"1":2,"2":"DATETIME"}">
DATETIME
</td>
<td>
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| End_Date | <td data-sheets-value="{"1":2,"2":"DATETIME"}">
DATETIME
</td>
<td>
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Teams |
| Column Name | <td data-sheets-value="{"1":2,"2":"Data Type"}">
Data Type
</td>
<td data-sheets-value="{"1":2,"2":"Data Length"}">
Data Length
</td>
<td data-sheets-value="{"1":2,"2":"PK"}">
PK
</td>
<td data-sheets-value="{"1":2,"2":"FK"}">
FK
</td>
<td data-sheets-value="{"1":2,"2":"Identity"}">
Identity
</td>
<td data-sheets-value="{"1":2,"2":"Default"}">
Default
</td>
| Team_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Team_Name | <td data-sheets-value="{"1":2,"2":"VARCHAR"}">
VARCHAR
</td>
<td data-sheets-value="{"1":3,"3":20}">
20
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Active | <td data-sheets-value="{"1":2,"2":"BIT"}">
BIT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":3,"3":1}">
1
</td>
| Team_Engine |
| Column Name | <td data-sheets-value="{"1":2,"2":"Data Type"}">
Data Type
</td>
<td data-sheets-value="{"1":2,"2":"Data Length"}">
Data Length
</td>
<td data-sheets-value="{"1":2,"2":"PK"}">
PK
</td>
<td data-sheets-value="{"1":2,"2":"FK"}">
FK
</td>
<td data-sheets-value="{"1":2,"2":"Identity"}">
Identity
</td>
<td data-sheets-value="{"1":2,"2":"Default"}">
Default
</td>
| Team_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Engine | <td data-sheets-value="{"1":2,"2":"VARCHAR"}">
VARCHAR
</td>
<td data-sheets-value="{"1":3,"3":15}">
15
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Start_Date | <td data-sheets-value="{"1":2,"2":"DATETIME"}">
DATETIME
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| End_Date | <td data-sheets-value="{"1":2,"2":"DATETIME"}">
DATETIME
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Circuit |
| Column Name | <td data-sheets-value="{"1":2,"2":"Data Type"}">
Data Type
</td>
<td data-sheets-value="{"1":2,"2":"Data Length"}">
Data Length
</td>
<td data-sheets-value="{"1":2,"2":"PK"}">
PK
</td>
<td data-sheets-value="{"1":2,"2":"FK"}">
FK
</td>
<td data-sheets-value="{"1":2,"2":"Identity"}">
Identity
</td>
<td data-sheets-value="{"1":2,"2":"Default"}">
Default
</td>
| Circuit_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Circuit_Name | <td data-sheets-value="{"1":2,"2":"NVARCHAR"}">
NVARCHAR
</td>
<td data-sheets-value="{"1":3,"3":60}">
60
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Circuit_Type | <td data-sheets-value="{"1":2,"2":"VARCHAR"}">
VARCHAR
</td>
<td data-sheets-value="{"1":3,"3":15}">
15
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Direction | <td data-sheets-value="{"1":2,"2":"VARCHAR"}">
VARCHAR
</td>
<td data-sheets-value="{"1":3,"3":50}">
50
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Circuit_Location | <td data-sheets-value="{"1":2,"2":"NVARCHAR"}">
NVARCHAR
</td>
<td data-sheets-value="{"1":3,"3":50}">
50
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Last_Length_Used | <td data-sheets-value="{"1":2,"2":"DECIMAL"}">
DECIMAL
</td>
<td data-sheets-value="{"1":2,"2":"5,3"}">
5,3
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Grand_Prix_Name | <td data-sheets-value="{"1":2,"2":"NVARCHAR"}">
NVARCHAR
</td>
<td data-sheets-value="{"1":3,"3":70}">
70
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Start_Date | <td data-sheets-value="{"1":2,"2":"DATETIME"}">
DATETIME
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| End_Date | <td data-sheets-value="{"1":2,"2":"DATETIME"}">
DATETIME
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Race Types |
| Column Name | <td data-sheets-value="{"1":2,"2":"Data Type"}">
Data Type
</td>
<td data-sheets-value="{"1":2,"2":"Data Length"}">
Data Length
</td>
<td data-sheets-value="{"1":2,"2":"PK"}">
PK
</td>
<td data-sheets-value="{"1":2,"2":"FK"}">
FK
</td>
<td data-sheets-value="{"1":2,"2":"Identity"}">
Identity
</td>
<td data-sheets-value="{"1":2,"2":"Default"}">
Default
</td>
| Race_Type_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Race_Type | <td data-sheets-value="{"1":2,"2":"NVARCHAR"}">
NVARCHAR
</td>
<td data-sheets-value="{"1":3,"3":10}">
10
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Race |
| Column Name | <td data-sheets-value="{"1":2,"2":"Data Type"}">
Data Type
</td>
<td data-sheets-value="{"1":2,"2":"Data Length"}">
Data Length
</td>
<td data-sheets-value="{"1":2,"2":"PK"}">
PK
</td>
<td data-sheets-value="{"1":2,"2":"FK"}">
FK
</td>
<td data-sheets-value="{"1":2,"2":"Identity"}">
Identity
</td>
<td data-sheets-value="{"1":2,"2":"Default"}">
Default
</td>
| Race_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td>
</td>
| Race_Date | <td data-sheets-value="{"1":2,"2":"DATETIME"}">
DATETIME
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Driver_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Circuit_ID | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"Yes"}">
Yes
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Final_Position | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Points | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
| Race_Type | <td data-sheets-value="{"1":2,"2":"TINYINT"}">
TINYINT
</td>
<td>
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td data-sheets-value="{"1":2,"2":"No"}">
No
</td>
<td>
</td>
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;
