Building a Formula One Race Tracker – Part Two

Posted On: In: Microsoft SQL Server

Contents

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

The Database Schema

In Part One we looked at the project scope, why I was building this project, how the database was going to look, the columns and their respective datatypes and a mock-up of the application design, in this part I am going to show you how I went about populating the database with the objects required and then filling them objects with referential data.

First up I need to create a database to hold all of my objects, along with that I also need to create a login which will be used later on in the project to connect to the database, remembering to change the password to something other than what I have specified here.

The driver’s table will hold all the information about the drivers;

The table will also have a primary key which I have set as the driver_id I haven’t given the primary key an identity because I want to specify my integer ids for drivers.

Much like the driver’s table, this one hold’s all the teams;

Just like the driver’s table, there is no identity column specified for this table as I want to specify my own integer ID’s for Team_ID which is also the primary key of this table.

The driver team table is a link table, this table is going to link all of our drivers to our teams,

I have also specified a start and end date for each driver with each team in this link table, this is because Formula One drivers can start and end with a particular team after each season.

Note: If the End_Date is not populated the driver is still driving for the specified team.

As with drivers, teams in Formula One can often change who’s engine they are using in their car from season to season, with this in mind I need to be able to record this, the link table Team_Engine will allow us to do that.

The Team_ID a foreign key which references the team’s table. The start date and end date work in the same way as they do in Driver_Team.

Note: If the End_Date is not populated the engine listed is still being used by the team specified.

The Circuit table is going to hold all of the information about the circuits

There are a small number of the specified columns used in the final application, I have captured more information than required allowing for further reporting should that be something I want to do a bit later.

Note: If the End_Date is not populated the circuit is still included in the season roster.

Race Types is a referential table which is going to hold the race types, this will include practice & qualification sessions, along with the actual race event, the reason for this is so I can record each stage of the drivers’ weekend in the race table and differentiate between the race and the pre-race events.

Race_Type_ID is the primary key in this table again there is no identity specified as I want each referential row to have an integer id of my choosing.

The race table is where all of the weekend race data will get recorded, in this table we have Race_ID as the primary key however it has an identity set, starting at 1 and incrementing by 1 each time a row is inserted.

Reference Data

The referential data that we are going to use in this project is all publically available, the majority of which can be obtained from Wikipedia. Below you will find all the data sets required for this project.

I did have to do some work to get the data into a workable state though, which I did using Google Sheets. I inserted all of the data into a sheet, one sheet for each of the referential tables I needed to build and then built a SQL insert statement directly inside Google Sheets for each row using a formula similar to this;

Which looked like this when complete;

I have included the source to all of the referential data under each insert statement.

Source

Source

The race types I populated manually as there is no source available for this data

Source

The Team_Engine table is made up of one row for each team, followed by the Engine they are currently using with a start date of the insert date, this can be changed to the date that the team actually started to use that engine if required, end date is left unpopulated as each team is currently using the engine’s specified.

The reference data for the Driver_Team link table is built by taking the Drivers Team_ID from the Teams table and the ID of the Driver from the driver’s table and inserting them into a single row per driver, the result of which can be seen below.

Driver Team Table

The Keys

Now that I have all of the tables created and the data populated I need to make sure that all the keys are applied, as per the design I need to apply a number of primary keys to the defined columns in each table, this has to be done before the foreign keys can be defined.

Now that the primary keys are set I can specify the foreign keys for the tables that require them

The reason that I like to add the constraints for primary and foreign keys outside of the table creation is that it allows me to specify a custom name for the constraint because if I was to simply execute

SQL Server would specify a key name for me which when troubleshooting error’s relating to key constraints in my queries makes finding the offending key difficult, especially in larger tables with many keys spanning many tables.

Let’s Test

Now that the table schema is created and we have populated the referential data that we require we can run a small test against that data to make sure that the data we expect is returned, below is a query that will return all drivers and their current team from the Driver_Team link table making use of the foreign keys.

You should get some like this in the output

Driver Team Sample Query Output

In future seasons if a driver moves between teams each driver will have multiple rows returned but as explained above the current team will be the one where the End_Date is NULL.

Next up is how I built the stored procedures needed to get the data in and out of the database.