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}.