Database and security
Database Design
The database design is made so that every team member knows the structure of the database. It basically shows you the way we organize our data.
This is the database setup we will use for the RegterschotRacing API. A full description of every tables usage and datatypes can be found below. All green boxes will be made by Smalltalk in order to provide a sound foundation to build an API on. The red boxes could be implemented for future expansions, but they are out of our current scope. These have been placed here as an example to provide future developers with a stepping stone.
Database Glossary
Tablenames and formats are written in italic; Columns that contain the primary key are underlined.
| Table | Column | Datatype | Null / Not Null | Comments |
|---|---|---|---|---|
| Graph | This table contains all data that is required to show a graph. It contains the sensor it is displaying the data for, and the type of graph it is. | |||
| GraphID | int | Not null | This is the unique identifier of the graph. | |
| SensorID | int | Not null | The unique identifier of the sensor that the graph is displaying the data of. | |
| Race | This table contains all information relevant to a race. | |||
| RaceID | int | Not null | The unique identifier of the race. | |
| NumberOfLaps | int | Not null | The number of rounds that the race possesses. | |
| RaceName | varchar(255) | Not null | The name of the race. | |
| Date | DATETIME | Not null | The date of the start of the race. | |
| Sensor | This table functions as collection of all sensors. | |||
| SensorID | int | Not null | The unique identifier of the sensor. | |
| SensorName | varchar(255) | Not null | The name of the sensor. | |
| Type | varchar(255) | Not null | The type of graph, can be anything from the following:
| |
Tab | This table contains all information that is relevant to a Tab. | |||
| TabID | int | Not null | The unique identifier of the tab. | |
| TabName | varchar(255) | Not null | The name of the tab. The name is set when creating a new tab in the frontend web-application. | |
| RaceID | int | Not null | The unique identifier of the race that the tab belongs to. | |
| UserID | int | Not null | The unique identifier of the user that the tab belongs to. | |
| User | This table handles all user data and other data relevant to have users that can interact with the application. | |||
| UserID | int | Not null | The unique identifier of the user. | |
| Username | varchar(255) | Not null | The unique name of a user that will be on display in the frontend web-application. | |
| Password | varchar(255) | Not null | The hashed password of the user that is needed when logging in into the web-application. | |
| GaugeSettings | This table handles all the data needed to draw a gauge chart. | |||
| SensorID | int | Not null | The unique identifier of the sensor. Also used here as unique identifier. | |
| Min | int | Not null | The minimal value that the gauge chart has. | |
| Max | int | Not null | The maximal value that the gauge chart has. | |
| GreenTo | int | Not null | The value where the green zone ends. | |
| YellowTo | int | Not null | The value where the yellow zone ends | |
Design decisions related to the database
Decision 1 | Description |
|---|---|
Problem/Issue | Throwing all of the different sensors and their data into one big table will quickly create a very clumped and hard-to-read table. |
| Decision | Create a different table for every sensor and add a new table that contains all sensor IDs. |
| Alternatives | - |
| Arguments | By dividing data over multiple tables, query time can be shortened significantly and keeps all of our tables in line with the normalization principle. |
| Decision 2 | Description |
|---|---|
| Problem/issue | A driver is only driving a couple of rounds in a race, which will create quite a confusing table if thrown into a single race tables. |
| Decision | Divide the different datatypes into multiple tables: a rounds table, a race table and a driver table. Create a linking table that links all of these three tables together. |
| Alternatives | - |
| Arguments | By dividing the table into a multitude of different tables, there's three distinct tables that are in line with the normalization principle. |
| Decision 3 | Description |
|---|---|
| Problem/issue | When adding a new graph to a tab the system must know what type of graph must be drawn for that sensor. |
| Decision | We decided to add the graph type to the sensor in the sensor table. |
| Alternatives |
|
| Arguments | Currently every sensor will have one specific type of graph. Later this could be changed to a user having to make a choice. Using the code we have currently and using this database design this change has less impact compared to the alternative. Beisdes the change having less of an impact we can also make the adding process smoother since a user wouldn't have to choose from all the possible graphs but only from the graphs that make sense for this sensor. Lastly we are able to make some query's faster and more compact this way. |
| Decision 4 | Description |
|---|---|
| Problem/issue | When adding a gauge graph to the database the settings need to be set as well such as its min and max value. |
| Decision | Adding the GaugeSettings table to the database. |
| Alternatives |
|
| Arguments | We decided to add a specific table for the settings of the gauge since these can be very different. The other option would have been to use all the same settings. These settings would for example have been min: 0 max:100 and every value would probably be calculated to a percentage. Using this system it is fully customizable, there are no additional calculations and the zones (green yellow and red) can be set and customized using this. For example one gauge might need a red zone from 90 - 100 whilst another already starts at 50. It is some extra data to fetch from the database but it is still faster than calculating every value multiple times a second. Besides that the data in the gauge would be more reliable. |
Security decisions
Regterschot indicated we do not have to worry too much about security yet, as they want to have a functional web application first. Therefore we are only implementing security measures for very high security risk scenarios.. Because of this we have chosen to hash the password, we do this because we are legally obliged to do. In addition to this, we have chosen to use a Json Web Token. We do this to ensure that unwanted people can not make calls to the API and only retrieve the data through the Web application. Further more we have choosen to make use of prepared statements. This is done to prevent SQL injections from happening. The prevention of SQL injections is implemented because it is seen as a very high security risk.