How to Generate ERD from Redshift Database

Reverse engineer ERD from database in Redshift

In order to walk through this tutorial, please setup a new database in Redshift first. In this tutorial, we will interact with a database named Tutorial01. You may use any name you like. Once the database is created, execute the following scripts to create database tables in database. In the next section we will form an ERD from these tables.

CREATE TABLE Item (ID INT NOT NULL IDENTITY, name varchar(255), price float4, BrandID int4 NOT NULL, CatagoryID int4 NOT NULL, PRIMARY KEY (ID));

CREATE TABLE ItemVariant (ID INT NOT NULL IDENTITY, detail varchar(255), color varchar(30), "size" varchar(50), qty int4, ItemID int4 NOT NULL, PRIMARY KEY (ID));

CREATE TABLE Brand (ID INT NOT NULL IDENTITY, name varchar(255), PRIMARY KEY (ID));

CREATE TABLE Catagory (ID INT NOT NULL IDENTITY, name varchar(255), PRIMARY KEY (ID));

CREATE TABLE Customer (ID INT NOT NULL IDENTITY, firstName varchar(50), lastName varchar(50), email varchar(50), address varchar(255), phone varchar(50), PRIMARY KEY (ID));

CREATE TABLE "Order" (ID INT NOT NULL IDENTITY, CustomerID int4 NOT NULL, orderDate date, PRIMARY KEY (ID));

CREATE TABLE OrderLine (OrderID int4 NOT NULL, ItemVariantID int4 NOT NULL, qty int4, PRIMARY KEY (OrderID, ItemVariantID));

ALTER TABLE ItemVariant ADD CONSTRAINT FKItemVarian766691 FOREIGN KEY (ItemID) REFERENCES Item (ID);

ALTER TABLE Item ADD CONSTRAINT FKItem119940 FOREIGN KEY (BrandID) REFERENCES Brand (ID);

ALTER TABLE Item ADD CONSTRAINT FKItem265973 FOREIGN KEY (CatagoryID) REFERENCES Catagory (ID);

ALTER TABLE "Order" ADD CONSTRAINT FKOrder556711 FOREIGN KEY (CustomerID) REFERENCES Customer (ID);

ALTER TABLE OrderLine ADD CONSTRAINT FKOrderLine150838 FOREIGN KEY (OrderID) REFERENCES "Order" (ID);

ALTER TABLE OrderLine ADD CONSTRAINT FKOrderLine292294 FOREIGN KEY (ItemVariantID) REFERENCES ItemVariant (ID);

Reverse engineer ERD from database in Redshift

To reverse database:

Select Tools > DB > Reverse Database from the application toolbar.
In the Database to Data Model window, click Next.
In the Database Configuration screen, select Redshift to be the database driver.
Provide the JDBC Driver File. You may click here to download the Amazon Redshift JDBC driver
Fill in the hostname, port, username and password of your Redshift database. Again, in this tutorial we are using a database named tutorial01.
Click Next.
Narrow down the scope of reversal by selecting the public schema.
Click Next.
Keep the tables selected in the Selecting Tables screen. The tables are the result of executing the SQL statements in the previous section.
Click Next.
Click Finish in the Reverse Database Preview screen.
A blank ERD will be created, will the Reversed Entities window popped out. The entities formed from the Redshift database are listed in the window. Now, select the entities and drag them onto the diagram. You can perform a multiple selection of entity by pressing the Ctrl or Shift key.

When you release your mouse button you will see the ERD formed from the entities. Tidy up the diagram content. Your diagram should look like the one below.