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

    Pros

    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

    Cons

    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

    Pros

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

    Cons

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

    Examples

    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

    Pros

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

    Cons

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

    Examples

    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

    Pros

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

    Cons

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

    Examples

    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]

    Pros

    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

    Cons

    1. Limited ERD capabilities

    Examples

    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

    Pros

    1. You are probably already using one

    Cons

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

    Examples

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

    Summary

    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

    Product Manager and founder of Dataedo. For many years business analyst, software architect and project manager in various industries - asset management, heavy industry, telco, utilities/gas and tourism. His specialties are data warehousing/BI and business applications.

    0
    There are no comments. Click here to write the first comment.