Step by Step • ERDbuilder

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)
)

st_id	dep_id	student	email	dob
hu1	water	Huey	hubert.duck	04-15
de2	evil	Dewey	dewfort.duck	04-15
lo3	values	Louie	llewellyn.duck	04-15

crs_id	fac_id	dep_id	course
water101	02do	water	Swimming
evil205	03pe	evil	Human-chasing
water202	04mi	water	Dives

crs_id	st_id	final_grade
water101	hu1	B
evil205	hu1	A
evil205	de2	A
water202	de2	F

dep_id	department
water	Water activities
evil	Evil procurement
values	Good values

faculty_name	title	fac_id	dep_id
Scrooge McDuck	Emeritus	01sc	water
Donald	Full	02do	water
Pete	Assistant	03pe	evil
Mickey	Full	04mi	values

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)