Jump to Menu

How to Generate ERD from Redshift Database

You can reverse engineer Entity Relationship Diagram (ERD) from an Amazon Redshift database. Doing so provides you with a database design diagram that can be used for analysis of database structure, for communication with teammates and for archiving. In this tutorial, you will form an ERD from a simple online bike shop database.

Compatible edition(s): Enterprise, Professional, Standard

  • April 08, 2016
  • Views: 62,277
  • PDF

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:

  1. Select Tools > DB > Reverse Database from the application toolbar.
  2. In the Database to Data Model window, click Next.
    Database to data model
  3. In the Database Configuration screen, select Redshift to be the database driver.
    Select Redshift database
  4. Provide the JDBC Driver File. You may click here to download the Amazon Redshift JDBC driver
    Driver file specified
  5. Fill in the hostname, port, username and password of your Redshift database. Again, in this tutorial we are using a database named tutorial01.
    Database connection specified
  6. Click Next.
  7. Narrow down the scope of reversal by selecting the public schema.
    Select schema
  8. Click Next.
  9. Keep the tables selected in the Selecting Tables screen. The tables are the result of executing the SQL statements in the previous section.
    Select tables
  10. Click Next.
  11. Click Finish in the Reverse Database Preview screen.
    Reverse database preview
  12. 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.
    Drag entities to diagram
    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.
    ERD formed from Redshift database


Trademark Disclaimer

Amazon Redshift is a trademark of Amazon Web Services


Turn every software project into a successful one.

We use cookies to offer you a better experience. By visiting our website, you agree to the use of cookies as described in our Cookie Policy.

OK