As a database professional, the ER diagram should be an essential part of your toolkit. In this article, we’ll briefly examine data modeling and which ERD tools best meet database design requirements.
What is data modeling, and how does an entity-relationship diagram (ER diagram or ERD) support it?
'Data modeling' is the process of arranging all the related data in an information system graphically, starting from scratch and going step by step through different phases to develop the physical database.
In data modeling, you have to go through three levels: conceptual, logical, and physical. The conceptual level is where you start the initial outline of the data model; it’s a very high-level summary of your entire data model. When you come to the logical level, the entities are expanded to include more details, such as entity attributes. In physical data modeling, you develop a complete model that’s ready to implement into the physical database. You can learn more about conceptual, logical, and physical data models here.
As I mentioned above, an ER diagram supports all the data modeling levels. As its name says, an entity-relationship diagram mainly consists of entities and the relationships between them. Entities can contain many components that represent data details, such as attributes, primary keys that uniquely identify data entities, foreign keys that create the relationship between entities, etc. Many standard notations are available – including Baker's, Crow's Foot, and IDEF1X – to model these components in your ER diagram.
Let's come to the most vital discussion in this article: the programs that we can use to create our ER diagrams. You can draw your ER diagram using a pencil and paper, but to save time and money, many people use advanced ERD tools.
It's time to explore a few ERD modeling tools with modern automated features that can improve your productivity.
Our 7 Favorite ERD Tools
Vertabelo is an online ERD tool with many modern automated features. With it, you can model your ER diagram from anywhere on any platform. This tool supports many popular databases, including MySQL, Oracle, SQL Server, PostgreSQL, etc.
Vertabelo provides you with a user-friendly, clean interface to model your data from the conceptual to the physical phases. Also, this online ERD tool supports many popular notations, including Crow's Foot, IDEF1X, and UML.
As an online ERD modeling tool, Vertabelo enhances team collaboration, especially for remote or distributed teams. You can learn more about sharing your Vertabelo model here. Also, you can use its SQL generation feature to create or remove elements in your physical database from your physical data model. Its reverse engineering feature lets you create a physical model of an existing database. Other handy data modeling features include model validation, built-in version control, and version control with GIT.
2. ER Studio
ER Studio is an offline, Windows-based data architecture and database design tool. It supports the MySQL, SQL Server, Sybase, Oracle, and DB2 database management systems (DBMSs). It also works with cloud services like Amazon RDS & S3, Azure SQL Database, Azure Blob storage, Google Cloud Databases, Oracle MySQL Cloud Service, etc.
This ERD modeling tool comes with the required notations to draw conceptual, logical, and physical models. It also provides an automated feature to create your physical model from the logical model. The tool also checks your model’s normalization and compliance with the target database.
With the reverse and forward engineering features that come with ER Studio, you can create your physical data model from an existing database or generate a DDL script from your physical model to make your physical database.
The advanced bi-directional compare and merge feature makes it easy to do revisions between models and databases.
3. Visual Paradigm
Visual Paradigm is a multi-diagramming tool that supports many popular databases, such as MySQL, SQL Server, Oracle, MariaDB, etc. It comes with Crow's Foot and other notations so you can draw your ER diagram from the conceptual level to the physical level.
The Table Record Editor and the Model Transistor are two key features of this ERD modeling tool. Table Record Editor lets you enter sample data and helps you understand the nature of data that will be entered in the physical database later. You can create logical and physical data models from their previous levels and maintain traceability with the Model Transistor feature.
Visual Paradigm provides a forward engineering feature to create SQL DDL files from your physical data model. Also, it compares your physical database against the physical data model and generates SQL scripts to patch the database. Reverse engineering lets you create a physical data model from an existing database, which you can modify in this modeler.
Also, Visual Paradigm offers an online edition that supports remote and collaborative work with your team.
Navicat comes as an offline ERD tool for Windows, Linux, and Mac OS. This ERD modeling tool also supports many major DBMSs like MySQL, SQL Server, Oracle, MariaDB, etc. Navicat provides you with three standard notations and with many other automated features to draw your conceptual to physical ER diagrams.
This ERD tool’s Export SQL feature allows you to create SQL scripts for each component of your physical data model. It also offers reverse and forward engineering.
5. Astah Professional
Astah Professional is an offline multi-diagramming tool that supports drawing various diagrams, including ER diagrams. This ERD tool comes in Windows, macOS, Ubuntu, and CentOS versions. You can download the version compatible with your operating system, install it, and start data modeling.
Astah Professional supports IDEF1X and Crow's Foot notation, reverse engineering, and SQL export (to export diagram entities in SQL-92).
Because this is an offline tool, it does not readily support collaborative work and model sharing.
SqlDBM is an online ERD modeling tool that supports the MySQL, SQL Server, and Amazon Redshift DBMSs. It allows you to create conceptual and physical data models using the Crow's Foot and IDEF1X notations.
As an online ERD tool, SqlDBM facilitates collaborative work on different platforms. You can share your data models with your team members by providing their email addresses; you can change access levels by checking or unchecking the options.
SqlDBM provides a version control feature and a Compare Revisions feature. It also offers forward and reverse engineering.
ERDPlus is an online ER diagram modeling tool that supports MySQL, SQL Server, Oracle, IBM DB2, and other DBMSs. This ERD tool uses Crow's Foot notation and lets you generate SQL DDL files to create your physical database from the physical data model.
Like other online ERD tools, ERDPlus lets you model your data from anywhere, on any device or platform.
Choosing the Best ERD Tools
The entity-relationship diagram plays a major role in the data modeling process. There are hundreds of online and offline ERD tools available, but choosing the best data modeling tool can be challenging. In this article, I’ve listed some ERD tools; you can learn more about selecting the best programs to create your ER diagram here.