16 Dec 2019
Create the database
- Part 1 - Introduction
- Part 2 - Getting Started
- Part 3 - Creating a basic design
- Part 4 - Upgrade to .NET Core 3.1
- Part 5 - Database test
- Part 6 - This Article
- Part 7 - No architecture deep dive
- Part 8 - First API mockup
- Part 9 - A new start
- Part 10 - Create the database scripts
- Part 11 - Create the group controller and service
- Part 12 - Running SQL Server in Docker
- Part 13 - Being able to use two database engines
The draw for Euro 2020 is done and the schedule has been published by EUFA. This means that the database can be created and filled based on this.
Creating the database
In the early staged of the development I will use SQLite, but when the site matures I need to use another SQL engine.
Those engines understand the same basic SQL syntax, but they all differ when it comes to the CREATE TABLE command.
Data types and the syntax to define foreign keys may differ.
I created a file to create the tables and a separate file to fill those tables.
When I’ll create the final database I probably only need to adjust tables.sql for the specific database engine.
The INSERT commands are less likely to change.
I’ll be needing Dutch content for my own use but for everyone who want to follow along or use the project themselves I created an English version as well. Translations into other languages should be easy.
These files are committed to the repository.
To create the SQLite database with these files use these commands:
sqlite3 database.sqlite < tables.sql
sqlite3 database.sqlite < 2020-fixtures-en.sql
What’s next?
Next I want to create the first API to retrieve the matches in a group: GET /input/group/{groupid}.
Finally!