Step by Step"
In ERDbuilder: Entity Relationship Diagrams Builder

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

Building an ERD is easy with ERDbuilder with the workflow presented in this vignette. In short, the steps are:

Display all entities
Define relationships
Specify key attributes
Define entity relationships
Adjust parameters

This vignette shows how to build an entity relationship diagram (ERD) step by step. The example includes five different entities.

Define data

Hypothetical course data in a college.

# Load Packages -----------------------------------------------------------

library(ERDbuilder)
library(gt)
library(dplyr)

# Define entities ---------------------------------------------------------

students_tbl <- data.frame(
  st_id = c("hu1", "de2", "lo3"),
  dep_id = c("water", "evil", "values"),
  student = c("Huey", "Dewey", "Louie"),
  email = c("hubert.duck", "dewfort.duck", "llewellyn.duck"),
  dob = c("04-15", "04-15", "04-15")
)

courses_tbl <- data.frame(
  crs_id = c("water101", "evil205", "water202"),
  fac_id = c("02do", "03pe", "04mi"),
  dep_id = c("water", "evil", "water"),
  course = c("Swimming", "Human-chasing", "Dives")
)

enrollment_tbl <- data.frame(
  crs_id = c("water101", "evil205", "evil205", "water202"),
  st_id = c("hu1", "hu1", "de2", "de2"),
  final_grade = c("B", "A", "A", "F")
)

department_tbl <- data.frame(
  dep_id = c("water", "evil", "values"),
  department = c("Water activities", "Evil procurement", "Good values")
)

faculty_tbl <- data.frame(
  faculty_name = c("Scrooge McDuck", "Donald", "Pete", "Mickey"),
  title = c("Emeritus", "Full", "Assistant", "Full"),
  fac_id = c("01sc", "02do", "03pe", "04mi"),
  dep_id = c("water", "water", "evil", "values")
)


my_gt <- function(df) {
  df |> 
    gt() |>
    tab_style(
      style = cell_fill(color = "darkolivegreen1"),
      locations = cells_column_labels()
    )
}

gt_group(
  my_gt(students_tbl),
  my_gt(courses_tbl),
  my_gt(enrollment_tbl),
  my_gt(department_tbl),
  my_gt(faculty_tbl)
)

Desired ERD

The following code is what we would like to end up with.

## Define relationships
relationships <- list(
  courses = list(
    enrollment = list(crs_id = "crs_id", relationship = c("||", "|<")),
    department = list(dep_id = "dep_id", relationship = c(">|", "||")),
    faculty = list(fac_id = "fac_id", relationship = c(">0", "||"))
  ),
  enrollment = list(
    students = list(st_id = "st_id", relationship = c(">0", "||")
    )
  ),
  students = list(
    department = list(dep_id = "dep_id", relationship = c(">|", "||"))
  ),
  faculty = list(
    department = list(dep_id = "dep_id", relationship = c(">|", "||"))
  )
)

## Create ERD object
erd_object <-
  create_erd(
    list(
      students = students_tbl,
      courses = courses_tbl,
      enrollment = enrollment_tbl,
      department = department_tbl,
      faculty = faculty_tbl
    ),
    relationships)

## Render ERD
render_erd(erd_object, label_distance = 0, label_angle = 15, n = 20)

Step 1: Display all entities

The first step is to print the nodes with the entities' attributes, even if they are not yet connected. Observe that the relationship list is empty.

## Define relationships
relationships <- list()

## Create ERD object
erd_object <-
  create_erd(
    list(
      students = students_tbl,
      courses = courses_tbl,
      enrollment = enrollment_tbl,
      department = department_tbl,
      faculty = faculty_tbl
    ),
    relationships)

## Render ERD
render_erd(erd_object, label_distance = 0, label_angle = 15, n = 20)

Step 2: Define relationships

In this step, the relationship between entities is established, although the nature of the relationships is not yet specified.

There are two attempts to establish the relationships; the first is discarded since the graph is too wide. The second attempt solves this by changing the order in which the relationships were specified. The vignette "Order of Entities" provides more details on how to tune the relationships.

## Define relationships: ugly attempt
relationships <- list(
  courses = list(
    enrollment = list(),
    department = list(),
    faculty = list()
  ),
  enrollment = list(
    students = list()
  ),
  students = list(
    department = list()
  ),
  department = list(
    faculty = list()
  )
)

erd_object <-
  create_erd(
    list(
      students = students_tbl,
      courses = courses_tbl,
      enrollment = enrollment_tbl,
      department = department_tbl,
      faculty = faculty_tbl
    ),
    relationships)

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


# 2. Define Relationships: improved example

## Define relationships
relationships <- list(
  courses = list(
    enrollment = list(),
    department = list(),
    faculty = list()
  ),
  enrollment = list(
    students = list()
  ),
  students = list(
    department = list()
  ),
  faculty = list(
    department = list()
  )
)

erd_object <-
  create_erd(
    list(
      students = students_tbl,
      courses = courses_tbl,
      enrollment = enrollment_tbl,
      department = department_tbl,
      faculty = faculty_tbl
    ),
    relationships)

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

Step 3: Highlight key attributes

Specify the keys that allow joining tables. This will change the font of those attributes to bold and put them on top pf the node table.

## Define relationships
relationships <- list(
  courses = list(
    enrollment = list(crs_id = "crs_id", relationship = c("", "")),
    department = list(dep_id = "dep_id", relationship = c("", "")),
    faculty = list(fac_id = "fac_id", relationship = c("", ""))
  ),
  enrollment = list(
    students = list(st_id = "st_id", relationship = c("", "")
    )
  ),
  students = list(
    department = list(dep_id = "dep_id", relationship = c("", ""))
  ),
  faculty = list(
    department = list(dep_id = "dep_id", relationship = c("", ""))
  )
)

## Create ERD object
erd_object <-
  create_erd(
    list(
      students = students_tbl,
      courses = courses_tbl,
      enrollment = enrollment_tbl,
      department = department_tbl,
      faculty = faculty_tbl
    ),
    relationships)

## Render ERD
render_erd(erd_object, label_distance = 0, label_angle = 15, n = 20)

Step 4: Define entity relationships

Then, add the nature of the relationships following the nomenclature table.

## Define relationships
relationships <- list(
  courses = list(
    enrollment = list(crs_id = "crs_id", relationship = c("||", "|<")),
    department = list(dep_id = "dep_id", relationship = c(">|", "||")),
    faculty = list(fac_id = "fac_id", relationship = c(">0", "||"))
  ),
  enrollment = list(
    students = list(st_id = "st_id", relationship = c(">0", "||")
    )
  ),
  students = list(
    department = list(dep_id = "dep_id", relationship = c(">|", "||"))
  ),
  faculty = list(
    department = list(dep_id = "dep_id", relationship = c(">|", "||"))
  )
)

## Create ERD object
erd_object <-
  create_erd(
    list(
      students = students_tbl,
      courses = courses_tbl,
      enrollment = enrollment_tbl,
      department = department_tbl,
      faculty = faculty_tbl
    ),
    relationships)

## Render ERD
render_erd(erd_object, label_distance = 0, label_angle = 15, n = 20)

Step 5: Adjust parameters

Specify the symbol distance to the edge with label_distance, the angle at which the edge labels are displayed with label_angle, and the maximum number of rows in each node table with n. The latter is particularly important when one of the tables has many attributes.

## Define relationships
relationships <- list(
  courses = list(
    enrollment = list(crs_id = "crs_id", relationship = c("||", "|<")),
    department = list(dep_id = "dep_id", relationship = c(">|", "||")),
    faculty = list(fac_id = "fac_id", relationship = c(">0", "||"))
  ),
  enrollment = list(
    students = list(st_id = "st_id", relationship = c(">0", "||")
    )
  ),
  students = list(
    department = list(dep_id = "dep_id", relationship = c(">|", "||"))
  ),
  faculty = list(
    department = list(dep_id = "dep_id", relationship = c(">|", "||"))
  )
)

## Create ERD object
erd_object <-
  create_erd(
    list(
      students = students_tbl,
      courses = courses_tbl,
      enrollment = enrollment_tbl,
      department = department_tbl,
      faculty = faculty_tbl
    ),
    relationships)

## Render ERD
render_erd(erd_object, label_distance = 2, label_angle = 50, n = 2)