The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

ERDbuilder ERDbuilder website

codecov CRAN status R-CMD-check pkgdown

ERDbuilder creates Entity-Relationship Diagrams (ERDs) directly from R data frames.

The package provides tools to:

Installation

You can install the development version of ERDbuilder from GitHub:

remotes::install_github("gbasulto/ERDbuilder")

Then load the package:

library(ERDbuilder)

A typical ERDbuilder workflow consists of six steps:

  1. Place the data frames in a named list.
  2. Use suggest_relationships() to identify likely relationships.
  3. Review and, when necessary, edit the suggested relationships.
  4. Create an ERD object with create_erd().
  5. Check the ERD with validate_erd().
  6. Render the ERD or use it to join tables.

Define dataframes for example:


# Create dataframe "employees"
employees <- data.frame(
  emp_id = c(1, 2, 3),
  name = c("Alice", "Bob", "Charlie")
)

# Create dataframe "departments"
departments <- data.frame(
  dept_id = c(1, 2),
  dept_name = c("HR", "Engineering")
)

# Create dataframe "assignments"
assignments <- data.frame(
  emp_id = c(1, 3),
  dept_id = c(1, 2)
)
df_list <- list(
  employees = employees,
  departments = departments,
  assignments = assignments
)

relationships <- suggest_relationships(df_list)
#> Suggested 2 relationship(s).

erd_object <- create_erd(
  df_list = df_list,
  relationships = relationships
)

validate_erd(erd_object)
#> ERD validation: valid 
#> Errors: 0 | Warnings: 0
render_erd(erd_object)

Relationship suggestions are based on the available data and should always be reviewed before they are treated as database constraints.

Main functions

Function Purpose
suggest_relationships() Suggest relationships between data frames
format_relationships() Convert a relationship specification into paste-ready R code
create_erd() Create an ERD object
validate_erd() Validate the ERD structure and optionally inspect observed cardinalities
is_valid_erd() Return whether an ERD passes validation
render_erd() Render an ERD
perform_join() Join tables using relationships stored in an ERD

Suggesting relationships

suggest_relationships() examines pairs of data frames and evaluates possible join columns using information such as:

Start by creating a named list of data frames:

employees <- data.frame(
  emp_id = c(1, 2, 3),
  name = c("Alice", "Bob", "Charlie")
)

departments <- data.frame(
  dept_id = c(1, 2),
  dept_name = c("Human Resources", "Engineering")
)

assignments <- data.frame(
  assignment_id = 1:3,
  emp_id = c(1, 1, 3),
  dept_id = c(1, 2, 2)
)

df_list <- list(
  employees = employees,
  departments = departments,
  assignments = assignments
)

Generate the suggested relationships:

relationships <- suggest_relationships(
  df_list,
  quiet = TRUE
)

relationships
#> $employees
#> $employees$assignments
#> $employees$assignments$emp_id
#> [1] "emp_id"
#> 
#> $employees$assignments$relationship
#> [1] "||" "0<"
#> 
#> 
#> 
#> $departments
#> $departments$assignments
#> $departments$assignments$dept_id
#> [1] "dept_id"
#> 
#> $departments$assignments$relationship
#> [1] "||" "|<"
#> 
#> 
#> 
#> attr(,"suggestions")
#>    from_table    to_table from_columns to_columns relationship_left
#> 1   employees assignments       emp_id     emp_id                ||
#> 2 departments assignments      dept_id    dept_id                ||
#>   relationship_right score coverage_from_to coverage_to_from coverage_mean
#> 1                 0< 0.925        0.6666667                1     0.8333333
#> 2                 |< 1.000        1.0000000                1     1.0000000
#>   from_unique to_unique n_columns
#> 1        TRUE     FALSE         1
#> 2        TRUE     FALSE         1

The returned object uses the same relationship structure expected by create_erd().

Diagnostic information about the suggestions is stored in the "suggestions" attribute:

attr(relationships, "suggestions")
#>    from_table    to_table from_columns to_columns relationship_left
#> 1   employees assignments       emp_id     emp_id                ||
#> 2 departments assignments      dept_id    dept_id                ||
#>   relationship_right score coverage_from_to coverage_to_from coverage_mean
#> 1                 0< 0.925        0.6666667                1     0.8333333
#> 2                 |< 1.000        1.0000000                1     1.0000000
#>   from_unique to_unique n_columns
#> 1        TRUE     FALSE         1
#> 2        TRUE     FALSE         1

These diagnostics can be used to review the proposed join columns, relationship direction, key uniqueness, value coverage, and confidence score.

Creating paste-ready relationship code

Use format_relationships() to convert the suggested object into R code:

cat(format_relationships(relationships))
#> relationships <- list(employees = list(assignments = list(emp_id = "emp_id", relationship = c("||", 
#> "0<"))), departments = list(assignments = list(dept_id = "dept_id", 
#>     relationship = c("||", "|<"))))

To copy that code directly to the system clipboard during an interactive R session, use:

relationships <- suggest_relationships(
  df_list,
  copy = TRUE
)

Clipboard support requires the optional clipr package:

install.packages("clipr")

Creating and validating an ERD

After reviewing the suggested relationships, create the ERD object:

erd_object <- create_erd(
  df_list = df_list,
  relationships = relationships
)

validation <- validate_erd(erd_object)

validation
#> ERD validation: valid 
#> Errors: 0 | Warnings: 0

The validation result contains an issue table:

validation$issues
#> [1] severity   code       from_table to_table   n_affected message   
#> <0 rows> (or 0-length row.names)

It also contains a logical indicator of whether the ERD is valid:

validation$valid
#> [1] TRUE

For a direct logical result, use:

is_valid_erd(erd_object)
#> [1] TRUE

Structural and data validation

By default, validate_erd() distinguishes between structural errors and warnings based on the observed data.

Structural errors include problems such as:

When check_data = TRUE, the function also compares the declared cardinalities with the values observed in the data:

validate_erd(
  erd_object,
  check_data = TRUE
)
#> ERD validation: valid 
#> Errors: 0 | Warnings: 0

Observed cardinality violations are warnings by default because a data sample may not fully represent the intended database constraints.

Use strict = TRUE to treat warnings as validation failures:

validate_erd(
  erd_object,
  check_data = TRUE,
  strict = TRUE
)
#> ERD validation: valid 
#> Errors: 0 | Warnings: 0

During progressive ERD construction, incomplete relationships can be permitted explicitly:

validate_erd(
  erd_object,
  check_data = FALSE,
  allow_incomplete = TRUE
)
#> ERD validation: valid 
#> Errors: 0 | Warnings: 0

Example 1: Basic ERD

The following example manually defines the relationships among employees, departments, and assignments.

library(ERDbuilder)

employees <- data.frame(
  emp_id = c(1, 2, 3),
  name = c("Alice", "Bob", "Charlie")
)

departments <- data.frame(
  dept_id = c(1, 2),
  dept_name = c("Human Resources", "Engineering")
)

assignments <- data.frame(
  assignment_id = 1:3,
  emp_id = c(1, 1, 3),
  dept_id = c(1, 2, 2)
)

relationships <- list(
  assignments = list(
    employees = list(
      emp_id = "emp_id",
      relationship = c(">|", "||")
    ),
    departments = list(
      dept_id = "dept_id",
      relationship = c(">0", "||")
    )
  )
)

erd_object <- create_erd(
  df_list = list(
    employees = employees,
    departments = departments,
    assignments = assignments
  ),
  relationships = relationships
)

validation <- validate_erd(
  erd_object,
  check_data = TRUE
)

validation
#> ERD validation: valid with warnings 
#> Errors: 0 | Warnings: 1 
#>  severity code                 from_table  to_table  n_affected
#>  warning  observed_cardinality assignments employees 1         
#>  message                                                                                                    
#>  The `assignments` side is declared `>|` (one or more), but 1 row(s) in `employees` violate that constraint.

Render the ERD:

erd_plot <- render_erd(
  erd_object,
  label_distance = 0,
  label_angle = -25
)

# erd_plot

To export the diagram, packages such as DiagrammeRsvg, rsvg, and tiff can be used:

library(DiagrammeRsvg)
library(rsvg)
library(tiff)

dpi <- 600
width_cm <- 38
height_cm <- 38

erd_plot |>
  DiagrammeRsvg::export_svg() |>
  charToRaw() |>
  rsvg::rsvg(
    width = width_cm * (dpi / 2.54),
    height = height_cm * (dpi / 2.54)
  ) |>
  tiff::writeTIFF("erd_plot.tiff")

Example 2: Crash, vehicle, occupant, and distraction data

This example creates a larger ERD and uses it to join four related tables.

library(ERDbuilder)
library(readr)

data_url <- paste0(
  "https://raw.githubusercontent.com/",
  "jwood-iastate/DataFiles/main/"
)

occupant_tbl <- read_csv(
  paste0(data_url, "OCC.csv"),
  show_col_types = FALSE
)

crash_tbl <- read_csv(
  paste0(data_url, "CRASH.csv"),
  show_col_types = FALSE
)

distract_tbl <- read_csv(
  paste0(data_url, "DISTRACT.csv"),
  show_col_types = FALSE
)

vehicle_tbl <- read_csv(
  paste0(data_url, "GV.csv"),
  show_col_types = FALSE
)

df_list <- list(
  Crash = crash_tbl,
  Vehicle = vehicle_tbl,
  Occupant = occupant_tbl,
  Distract = distract_tbl
)

The relationships can be suggested initially:

suggested_relationships <- suggest_relationships(df_list)

attr(suggested_relationships, "suggestions")

For this example, the reviewed relationship specification is:

relationships <- list(
  Crash = list(
    Vehicle = list(
      CASENUMBER = "CASENUMBER",
      relationship = c("||", "|<")
    ),
    Occupant = list(
      CASENUMBER = "CASENUMBER",
      relationship = c("||", "|<")
    ),
    Distract = list(
      CASENUMBER = "CASENUMBER",
      relationship = c("||", "0<")
    )
  ),
  Vehicle = list(
    Occupant = list(
      CASENUMBER = "CASENUMBER",
      VEHNO = "VEHNO",
      relationship = c("|0", "0<")
    ),
    Distract = list(
      CASENUMBER = "CASENUMBER",
      VEHNO = "VEHNO",
      relationship = c("||", "0<")
    )
  )
)

Create and validate the ERD:

erd_object <- create_erd(
  df_list = df_list,
  relationships = relationships
)

validate_erd(
  erd_object,
  check_data = TRUE
)

Join the tables:

# A many-to-many relationship may arise when Distract is joined after Crash,
# Vehicle, and Occupant have already been combined.

joined_data <- perform_join(
  erd_object,
  c("Crash", "Vehicle", "Occupant", "Distract")
)

Render the ERD:

erd_plot <- render_erd(
  erd_object,
  label_distance = 0,
  label_angle = 15,
  n = 20
)

erd_plot

Additional documentation

More detailed examples are available in the package vignettes:

browseVignettes("ERDbuilder")

The package website is available at:

https://gbasulto.github.io/ERDbuilder/

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.