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).