6 Different Categories of Tools You Can Use to Create Entity-Relationship Diagram (ERD) for Existing Database

Table of Contents:

    In this article I will present different categories of tools that you can use to create an ER diagram of your existing database to visualise its schema so that you can understand and communicate data you have.

    1. Pen and Paper

    Yes, I’m serious. You probably already have it on your desk. You know how to use it. It’s quick and fun. At least the first couple of entities are. If your database has more than, say, 10 key tables then forget it and move on to the next section.

    ER diagram written manually on piece of paper


    1. You can get quick results
    2. You know how to use it (from (pre)school)
    3. Easy to share - put it on the wall


    1. You can’t rearrange the entities
    2. Painful maintenance and changes
    3. Not that readable (depending on your hand writing)
    4. Not that easy to share, you need to take a picture to share with people outside of your office

    2. Generic Diagramming Tools (desktop / cloud)

    There’s a number of generic diagraming tools on the market that you can use to create all kinds of diagrams, like organization charts, UML diagrams, flow charts, mind maps and ER diagrams. Most popular tool in this category is MS Visio, part of MS Office family. Recently, desktop tools have been challenged with online cloud tools, such as Lucidchart.

    Many of the tools in this category allow you to connect to your database, import database structure and generate diagram automatically. It usually works well only once.

    ER diagram drawn with Lucidchart


    1. Advanced visual capabilities
    2. Auto arrange
    3. Easy online collaboration (in case of cloud tools)
    4. Easy sharing of diagrams


    1. Limited database support
    2. No link to data dictionary
    3. Don’t always handle changes correctly


    1. Desktop

      1. Visio
      2. Dia (free)
    2. Cloud

      1. Draw.io
      2. Lucidchart

    3. Classic Data Modeling Tools

    You probably know those tools. They have been around for decades. Data modeling tools. They are mostly old, clunky, outdated, but they get the job done. Designed to model data and design databases. They are also capable of reverse engineering and create a model out of live database.

    ER diagram in Erwin


    1. Rich ERD and data modeling capabilities
    2. Auto arrange
    3. Linked data dictionary in a repository


    1. Don’t always handle changes correctly (can destroy metadata created after first import)


    1. Erwin Data Modeler
    2. E/R Studio
    3. Visual Paradigm
    4. MySQL Workbench
    5. Oracle SQL Developer Data Modeler

    4. Cloud Database Design & Modeling Tools

    Recently, as most software, data modeling tools have moved to the cloud. There are some that migrated from desktop, like Oracle SQL Developer, and some built for cloud from scratch, like Vertabelo Database Modeler.

    ER diagram in Vertabelo


    1. Easy collaboration
    2. No installation required
    3. Rich ERD and data modeling capabilities
    4. Auto arrange


    1. Monthly subscription fee
    2. Don’t always handle changes correctly


    1. Vertabelo Database Modeler
    2. Oracle SQL Developer Data Modeler

    5. Database Documentation Tools

    You probably want to create a diagram to understand and communicate the design of a database. In that case you need not only a diagram, but other assets like a data dictionary (list of tables and columns), descriptions and a place to store more information, and then you want to share it in convenient readable and searchable format. You need a comprehensive database documentation. And for that you need a database documentation tool. Some of the tools (Dataedo, ShemaSpy) enable you to (or do it automatically) create a diagram (or multiple diagrams) for your database schema.

    ER diagram in Dataedo [sample]


    1. Convenient, searchable documentation of database schema in HTML format
    2. Provide quick results
    3. Ability to preserve knowledge about database in comments, and additional fields
    4. Great for sharing


    1. Limited ERD capabilities


    1. Dataedo
    2. SchemaSpy
    3. SQL Schema Explorer

    6. Database Management Tools/IDEs

    Some database management tools are equipped with either simple diagramming feature (e.g. SQL Server Management Studio) or have a separate data modeling module (e.g. MySQL Workbench or Oracle SQL Developer). The later work quite similar to data modeling tools and are only loosely coupled with database IDE.

    ER diagram in SSMS


    1. You are probably already using one


    1. Support for only one DBMS (not necessarily an issue)
    2. Some tools have limited ERD capabilities


    1. SQL Server Management Studio (SSMS)
    2. MySQL Workbench
    3. Oracle SQL Developer


    So that was my list of different categories of ERD tools. Choosing the right category and tool depends on a number aspects. Here’s a list of things you could consider in your choice:

    1. What tools you already have
    2. What tools you and your colleagues are already familiar with
    3. What database engine you want
    4. Do you need a diagram of the entire database, a subset, or multiple smaller diagrams
    5. Do you need to share or print the diagram
    6. Do you need to update the diagram after some time
    7. Do you need to create diagram for one or more databases and DBMSes
    8. Does your database have foreing keys to draw links or you’d need to add them manually
    9. What do you need a diagram for (that’s the tricky one)
    10. What budget you have for new tools
    11. How much time you have to get it done

    Those were just a few of the questions you could ask before picking a tool. If you need to document your database, communicate the design, keep diagram and documentation up to date with changes with little effort, document complex database environments, document database with no FKs, deal with confusing names (using aliases) and solve more problems I advise you check out Dataedo (it has a free trial).

    Piotr Kononow
    Piotr Kononow

