The foosball3 Database

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 rows

Disclaimer

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.

And Many More

As the diagram with all tables in the database show, there are many more tables. You can get details on each of these tables using [foosball3_meta_data()]. It will give a description for each table (next to each "CREATE" field_name) and all columns in the table.

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
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/RtmpPglTca/file1d956f30c458.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/RtmpPglTca/file1d956f30c458.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.