The goal of {dm} is to provide tools for working with multiple tables.

Skip to the Features section if you are familiar with relational data models.

Why?

The motivation for the {dm} package is a more sophisticated data management. {dm} uses the relational data model and its core concept of splitting one table into multiple tables.

As an example, we consider the nycflights13 dataset. This dataset contains five tables: the main flights table with links into the airlines, planes and airports tables, and a weather table without an explicit link.

The separation into multiple tables achieves several goals:

  • Avoid repetition, conserve memory: the information related to each airline, airport, and airplane are stored only once
    • name of each airline
    • name, location and altitude of each airport
    • manufacturer and number of seats for each airplane
  • Improve consistency: for updating any information (e.g. the name of an airport), it is sufficient to update in only one place
  • Segmentation: information is organized by topic, individual tables are smaller and easier to handle

The case for a relational data models for dplyr users

Users of the popular dplyr package for data wrangling mainly rely on dataframes. However, flat file systems like spreadsheets and dataframes can result in bloated tables, that hold many repetitive values. Worst case, you have a dataframe with multiple columns and in each row only a single value is changing. These users can benefit from a better data organization. The separation into multiple tables helps data quality but poses a different challenge: for each flight, the location of the origin airport, or the details on the airplane, are not available immediately but must be joined/merged:

#> # A tibble: 336,776 x 19
#>    month   day origin tailnum name    lat   lon   alt    tz dst   tzone
#>    <int> <int> <chr>  <chr>   <chr> <dbl> <dbl> <int> <dbl> <chr> <chr>
#>  1     1     1 EWR    N14228  Newa…  40.7 -74.2    18    -5 A     Amer…
#>  2     1     1 LGA    N24211  La G…  40.8 -73.9    22    -5 A     Amer…
#>  3     1     1 JFK    N619AA  John…  40.6 -73.8    13    -5 A     Amer…
#>  4     1     1 JFK    N804JB  John…  40.6 -73.8    13    -5 A     Amer…
#>  5     1     1 LGA    N668DN  La G…  40.8 -73.9    22    -5 A     Amer…
#>  6     1     1 EWR    N39463  Newa…  40.7 -74.2    18    -5 A     Amer…
#>  7     1     1 EWR    N516JB  Newa…  40.7 -74.2    18    -5 A     Amer…
#>  8     1     1 LGA    N829AS  La G…  40.8 -73.9    22    -5 A     Amer…
#>  9     1     1 JFK    N593JB  John…  40.6 -73.8    13    -5 A     Amer…
#> 10     1     1 LGA    N3ALAA  La G…  40.8 -73.9    22    -5 A     Amer…
#> # … with 336,766 more rows, and 8 more variables: year <int>, type <chr>,
#> #   manufacturer <chr>, model <chr>, engines <int>, seats <int>,
#> #   speed <int>, engine <chr>

This can result in long and inflated pipe chains full of left_join(), anti_join() and other forms of merging data.

{dm} offers a more elegant and shorter way to combine values by establishing key relations (see next section) while augmenting {dplyr}/{dbplyr} workflows.

Good to Know

Multiple, linked tables are a common concept in database management. Since many R users have a background in other disciplines, we present five important terms in relational data modeling to jump-start working with {dm}.

1) Model

A data model shows the structure between multiple tables that can be linked together. The nycflights13 relations can be transferred into the following graphical representation:

The flights table is linked to three other tables: airlines, planes and airports. By using directed arrows the visualization explicitly shows the connection between different columns (they are called attributes in the relational data sphere). For example: The column carrier in flights can be joined with the column carrier from the airlines table. Further Reading: The {dm} methods for visualizing data models.

The links between the tables are established through primary keys and foreign keys.

2) Primary Keys

In a relational data model every table needs to have one column/attribute that uniquely identifies a row. This column is called primary key (abbreviated with pk). A primary key can be either an existing column that satifies the condition of being unique or a new column that assigns an identifier.

In the airlines table of nycflights13 the column carrier is the primary key.

Further Reading: The {dm} package offers several function for dealing with primary keys.

3) Foreign Keys

The counterpart of a primary key in one table is the foreign key in another table. In order to join two tables, the primary key of the first table needs to be available in the second table, too. This second column is called the foreign key (abbreviated with fk).

For example, if you want to link the airlines table in the nycflights13 data to the flights table, the primary key in the airlines table is carrier which is present as foreign key carrier in the flights table.

Further Reading: The {dm} functions for working with foreign keys.

4) Normalization

Normalization is the technical term that describes the central design principle of a relational data model: splitting data into multiple tables. A normalized data schema consists of several relations (tables) that are linked with attributes (columns) with primary and foreign keys. One main goal is to keep the data organization as clean and simple as possible by avoiding redundant data entries.

For example, if you want to change the name of one airport in nycflights13, you have to change only a single data entry. Sometimes, this principle is called “single point of truth”.

See the Wikipedia article on database normalization for more details. Consider reviewing the Simple English version for a gentle introduction.

5) Relational Databases

dm is built upon relational data models, but it is not a database itself. Databases are systems for data management and many of them are constructed as relational databases, e.g. SQLite, MySQL, MSSQL, Postgres. As you can guess from the names of the databases SQL, the structured querying language plays an important role: It was invented for the purpose of querying relational databases.

Therefore, {dm} can copy data from and to databases, and works transparently with both in-memory data and with relational database systems.

Features

This package helps with many challenges that arise when working with relational data models.

Compound object

The dm class manages several related tables. It stores both the data and the metadata in a compound object, and defines operations on that object. These operations either affect the data (e.g., a filter), or the metadata (e.g., definition of keys or creation of a new table), or both.

  • data: a table source storing all tables
  • metadata: table names, column names, primary and foreign keys

This concept helps separating the join logic from the code: declare your relationships once, as part of your data, then use them in your code without repeating yourself.

Storage agnostic

The {dm} package augments {dplyr}/{dbplyr} workflows. Generally, if you can use {dplyr} on your data, it’s likely that you can use {dm} too. This includes local data frames, relational database systems, and many more.

Data preparation

A battery of utilities helps with creating a tidy relational data model.

  • Splitting and rejoining tables
  • Determining key candidates
  • Checking keys and cardinalities

Example

A readymade dm object with preset keys is included in the package:

#> ── Table source ───────────────────────────────────────────────────────────
#> src:  <package: nycflights13>
#> ── Data model ─────────────────────────────────────────────────────────────
#> Data model object:
#>   5 tables:  airlines, airports, flights, planes ... 
#>   53 columns
#>   3 primary keys
#>   3 references
#> ── Filters ────────────────────────────────────────────────────────────────
#> None

The cdm_draw() function creates a visualization of the entity relationship model:

cdm_nycflights13(cycle = TRUE) %>% 
  cdm_draw()

Filtering and joining

Similarly to dplyr::filter(), a filtering function cdm_filter() is available for dm objects. You need to provide the dm object, the table whose rows you want to filter, and the filter expression. A dm object is returned whose tables only contain rows that are related to the reduced rows in the filtered table. This currently only works for cycle-free relationships between the tables.

For joining two tables using their relationship defined in the dm, you can use cdm_join_tbl():

cdm_nycflights13(cycle = FALSE) %>%
  cdm_join_to_tbl(airports, flights, join = semi_join)
#> # A tibble: 336,776 x 19
#>     year month   day dep_time sched_dep_time dep_delay arr_time
#>    <int> <int> <int>    <int>          <int>     <dbl>    <int>
#>  1  2013     1     1      517            515         2      830
#>  2  2013     1     1      533            529         4      850
#>  3  2013     1     1      542            540         2      923
#>  4  2013     1     1      544            545        -1     1004
#>  5  2013     1     1      554            600        -6      812
#>  6  2013     1     1      554            558        -4      740
#>  7  2013     1     1      555            600        -5      913
#>  8  2013     1     1      557            600        -3      709
#>  9  2013     1     1      557            600        -3      838
#> 10  2013     1     1      558            600        -2      753
#> # … with 336,766 more rows, and 12 more variables: sched_arr_time <int>,
#> #   arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
#> #   faa <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
#> #   minute <dbl>, time_hour <dttm>

In our dm, the origin column of the flights table points to the airports table. Since all nycflights13-flights depart from New York, only these airports are included in the semi-join.

From and to databases

In order to transfer an existing dm object to a DB, you can call cdm_copy_to() with the target DB and the dm object:

#> ── Table source ───────────────────────────────────────────────────────────
#> src:  sqlite 3.29.0 [:memory:]
#> ── Data model ─────────────────────────────────────────────────────────────
#> Data model object:
#>   5 tables:  airlines, airports, flights, planes ... 
#>   53 columns
#>   3 primary keys
#>   4 references
#> ── Filters ────────────────────────────────────────────────────────────────
#> None

The key constraints from the original object are also copied to the newly created object. With the default setting set_key_constraints = TRUE for cdm_copy_to(), key constraints are also established on the target DB. Currently this feature is only supported for MSSQL and Postgres database management systems (DBMS).

It is also possible to automatically create a dm object from the permanent tables of a DB. Again, for now just MSSQL and Postgres are supported for this feature, so the next chunk is not evaluated. The support for other DBMS will be implemented in a future update.

More information

If you would like to learn more about {dm}, the Intro article is a good place to start. Further resources:

Standing on the shoulders of giants

This package follows the tidyverse principles:

  • dm objects are immutable (your data will never be overwritten in place)
  • many functions used on dm objects are pipeable (i.e., return new dm objects)
  • tidy evaluation is used (unquoted function parameters are supported)

The {dm} package builds heavily upon the {datamodelr} package, and upon the tidyverse. We’re looking forward to a good collaboration!

The {polyply} package has a similar intent with a slightly different interface.

The {data.cube} package has quite the same intent using array-like interface.

Articles in the {rquery} package discuss join controllers and join dependency sorting, with the intent to move the declaration of table relationships from code to data.

The {tidygraph} package stores a network as two related tables of nodes and edges, compatible with {dplyr} workflows.

In object-oriented programming languages, object-relational mapping is a similar concept that attempts to map a set of related tables to a class hierarchy.

Installation

Once on CRAN, the package can be installed with

Install the latest development version with


License: MIT © cynkra GmbH.

Funded by:

energie360° cynkra


Please note that the ‘dm’ project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.