Introduction
At the core of the foosball3 package lies the database. It is an SQLite relational database meant to store foosball tournaments and its results.
You can create a virtually blank database with
foosball3_create_db(), but you can also import existing
data using foosball3_import_db(). The latter let’s you
import data from previously exported foosball data. This can be an
SQLite database file, or a zip-archive containing all required data
tables (as CSV files).
This import function will create a copy of the database at the designated location, and will attempt to fix changes in database structure introduced since the database was last stored.
Let’s import the example data provided with the package, such that we can have a closer look at how the data is organised.
library(foosball3)
library(dplyr)
library(RSQLite)
example_file <- system.file(package = "foosball3", "example.zip")
target_file <- tempfile(fileext = ".db")
foosball3_import_db(example_file, target_file)Database Structure
In order to work with the database, we need to open a connection to it:
con <- dbConnect(SQLite(), target_file)If you have both dm and DiagrammeRsvg
installed, you will see a visualisation of the database relations
below.
From this scheme, a few important tables will be highlighted.
Tournaments
At the center of all tables is the table named ‘tournaments’. It contains a list of tournaments and some essential information about it, such as:
- Where is it held?
- When was it held?
- What kind of tournament is it?
tbl(con, "tournaments") |> collect()
#> # A tibble: 18 × 8
#> TOURNAMENT_ID TOURNAMENT_TYPE_CODE TOURNAMENT_DATE TOURNAMENT_DURATION
#> <int> <chr> <chr> <dbl>
#> 1 1 I 2013-11-21 1.5
#> 2 2 I 2014-11-20 1.5
#> 3 3 I 2015-10-29 1.5
#> 4 4 I 2016-11-03 1.5
#> 5 5 I 2017-11-30 1.08
#> # ℹ 13 more rows
#> # ℹ 4 more variables: POINT_SYSTEM_ID <int>, LOCATION_CODE <chr>,
#> # TOURNAMENT_STATE_CODE <chr>, TOURNAMENT_COMMENTS <chr>Technically, the database structure is capable of handling different
types of tournament structures, but the graphical user interface
([foosball3()]), currently only implements one type.
People
Another important table is the table named ‘persons’. It contains a list of people and their name. This list is used to enroll participants in tournament or assign then as tournament organisers. Note that a single individual can join multiple tournaments. For each tournament, a person is given a unique participant id. This means that a single person can have multiple participant ids.
tbl(con, "persons") |> collect()
#> # A tibble: 97 × 5
#> PERSON_ID PERSON_NAME GENDER_CODE QUALIFICATION_CODE HOME_BASE
#> <int> <chr> <chr> <chr> <chr>
#> 1 1 Deanna Troi F H QOQA
#> 2 2 Beverly Crusher F H QOQA
#> 3 3 Keiko O'Brien F H QOQA
#> 4 4 Q M H ROKI
#> 5 5 Geordi La Forge M H QOQA
#> # ℹ 92 more rowsDisclaimer
Note that the example data is based on real tournament data. To protect participant’s privacy, locations and names have been anonymised using Star Trek locations and characters.
Star Trek and all associated names are trademarks of CBS/Paramount. This open-source package is purely for educational/utility purposes and is not affiliated with, endorsed, or sponsored by the copyright holders.
An advantage of this approach is that statistics of individuals can be collected and these statistics can be used to balance players skill levels in matches.
Locations, Tables and Balls
The database records ‘locations’ (where tables are located, tournaments are organised, and where players are based); as well as ‘tables’ (and accompanying ‘periods’ describing when tables are where); and ‘balls’ to specify which ball is used during a match. You can of course use dummies if you don’t want to record these kinds of detail.
Views
All tables discussed above are plain tables, containing raw data. The advantage of having them in a database is that views of the data can be created. They can be used to simplify database complexity, don’t require physical storage of data.
Not all views will be presented here, but some notable ones will be addressed. For completeness all available views are listed here:
- match_positions
- matches_view
- opponent_results
- participant_match_results
- participant_tournament_results
- participants_view
- picture_tag_names
- player_combos
- player_statistics
- primary_tables
- table_statistics
- table_summary
- tournaments_view
You can use ‘participant_match_results’ to find results of a specific participant in any match of any tournament:
tbl(con, "participant_match_results")
#> # A query: ?? x 18
#> # Database: sqlite 3.53.3 [/tmp/Rtmp28OwwN/file1cb823b2589.db]
#> TOURNAMENT_ID TOURNAMENT_DATE LOCATION_NAME LOCATION_CITY TOURNAMENT_PHASE
#> <int> <chr> <chr> <chr> <chr>
#> 1 1 2013-11-21 First City Qo'noS Qualification
#> 2 1 2013-11-21 First City Qo'noS Qualification
#> 3 1 2013-11-21 First City Qo'noS Qualification
#> 4 1 2013-11-21 First City Qo'noS Qualification
#> 5 1 2013-11-21 First City Qo'noS Qualification
#> # ℹ more rows
#> # ℹ 13 more variables: MATCH_ID <int>, PARTICIPANT_ID <int>,
#> # POSITION_CODE <chr>, ROLE_CODE <chr>, SIDE_ID <int>, PERSON_ID <int>,
#> # PARTICIPANT <chr>, RESULT <int>, RESULT_OPPONENT <int>,
#> # PENALTY_POINTS <int>, CONCLUSION <chr>, MATCH_POINTS <int>,
#> # SUCCESS_RATE <dbl>But you can also look for aggregated results. For instance, player statistics are summarised in ‘player_statistics’:
tbl(con, "player_statistics")
#> # A query: ?? x 14
#> # Database: sqlite 3.53.3 [/tmp/Rtmp28OwwN/file1cb823b2589.db]
#> PERSON_ID QUALIFICATION_CODE PARTICIPANT TOURNAMENT_PHASE GOALS_SCORED
#> <int> <chr> <chr> <chr> <int>
#> 1 52 H Alyssa Ogawa Consolation final 2
#> 2 36 H Duras Consolation final 7
#> 3 49 H Jean-Luc Picard Consolation final 20
#> 4 13 H K'Mpec Consolation final 5
#> 5 23 H Lal Consolation final 13
#> # ℹ more rows
#> # ℹ 9 more variables: GOALS_CONCEDED <int>, GOAL_RATIO <dbl>,
#> # SUCCESS_RATE <dbl>, QUALIFICATION_RATE <dbl>, CHAMPION_COUNT <lgl>,
#> # RUNNERUP_COUNT <lgl>, MATCHES_PLAYED <int>, TOURNAMENTS_PLAYED <int>,
#> # CITIES_PLAYED <int>Have fun exploring the provided example database, or collect your own tournament results.