Creating a SQL Entity Relationship Diagram (ERD)

James Opacich
May 25 · 4 min read

Using PostgreSQL and Pagila Database to practice creating and using ERDs

  1. Using pgAdmin 4 and PostgreSQL this article will demonstrate how to Create an Entity Relationship Diagram (ERD) of the Pagila Database to gain a greater understanding of the data you will be working with and to create a reference for which to work off of.
  2. There will display two separate methods for ERD creation. One is manually done and is much more time intensive but a better learning experience, the other is automatically done by pgAdmin 4 and is orders of magnitude faster and easier for those that are just looking for an ERD to reference when using the Pagila Database.
  3. There will be links to the necessary information, github repositories and completed ERD for your reference.

PRE-NOTE (If you want to create an ERD fast then read this)

That being said, I found it very useful to create my own ERD manually. It got me thinking about the process, the data types, constraints, and relationships in a way that I felt further solidified my knowledge of SQL.

PREREQUISITES

Please have pgAdmin 4 open at this point and your database ready to use.

STEPS FOR CREATING ERD AUTOMATICALLY

STEP 1: Generate ERD tool

Generate ERD

Right-click on your database. I have called my database Pagila in this case. Then find the “Generate ERD” option on the drop down list.

This will open a new window in pgAdmin 4 located where the query window would normally be.

Here a new ERD window and tab are created. This is where the ERD will be created.

STEP 2: Save and Name Your ERD

Step 1: Click on this icon to open up the save file browser/box
Step 2: Name your ERD by typing your desired name into the end of the file path. Then click Create to save the named file. I decided to name my ERD pagila_erd3 for this exercise.

STEP 3: Export Your ERD

Click on the “Download Image” icon, it will bring up a file browser where you can name your new image file and save it to a desired file on your computer.

MANUALLY CREATING YOUR OWN ERD

STEP 1: CREATE NEW ERD

Instead of clicking on the database name and then selecting to generate a new database you will instead click on the “Tools” dropdown at the top of the pgAdmin app window and select “Create New ERD.”

STEP 2: Save and Name Your ERD

You will see now that you will have a blank ERD Window to work from.
Step 1: Click on this icon to open up the save file browser/box
Step 2: Name your ERD buy typing your desired name into the end of the file path. Then click Create to save the named file. I decided to name my ERD pagila_erd3 for this exercise.

STEP 2: Create Your First Table

Click the plus-sign icon to add a table.
Name your table and click on the column tab.
Click on plus sign to add column and constraints
Enter in your column names, data-types and basic constraints here and hit ok to create the table diagram. If you want to dive deeper into constraints then click on the pencil icon next to each column.

IN CASE YOU WANT TO GET DEEPER WITH CONSTRAINTS

There is a definitions tab although this doesn’t seem to add any additional functionality.
The constraints tab does seem to add a few more parameters to add to your diagram.

STEP 3: Creating Relationships

To create a 1 to many relationship highlighting foreign key relationships click on the 1M icon. This will open a form for you to select columns corresponding to the tables and relationships you want to create.
To create a many-to-many relationship click the MM icon and a form will open allowing you to do the same as the previous picture.

You have now created an ERD. I recommend practicing by recreating from scratch the Pagila Database ERD. You can check your work by simply creating an automated ERD as outline above. I also have corralled a bunch of question sets here in my Pagila Questions Sets repository that will help you fine-tune your querying skills.

Geek Culture

Proud to geek out. Follow to join our +1M monthly readers.