Back to articles list
The top 10 tools you can use to build your entity-relationship models in 2021!
There are several relational modeling tools. A simple Google search yields dozens of options for all kinds of purposes. But the question remains: what is the best tool for professional use or in teams? Which tool will give me access to all the features I need without having to switch between multiple tools?
There are some resources to help you answer this question like this article, but we can look at more. We review ten powerful tools and find the best solution for you!
1. SQL DBM
Known as SQL Database Manager, this platform enables ER diagramming with a wide range of capabilities. It is easy to use, and its web interface works well with both small and large databases.
SQL DBM supports multiple operating systems (because it is web-based), supports collaborative modeling, has reverse and forward engineering, and lets you version database models. It supports Amazon Redshift, Snowflake, Microsoft Azure, Microsoft SQL Server, PostgreSQL, and MySQL.
Despite all these features, there are downsides. Take its price, for example. The most basic version costs $25.00, and the business licenses can cost as much as $3000.00. The best features are available only at contract prices that are not even published on the website. Its free version is minimal, allowing only reverse engineering, one project, and 14 days of trial.
Other downsides: it took us a while to "spot" some features, many popular databases are not supported, and its auto-layout feature doesn't work well with big models. It could also improve its sharing capabilities a bit. If you want to understand more on organizing your ER diagrams, you can read about it here.
Pros:
- Generates scripts from the model.
- Allows reverse and forward engineering.
- Supports collaborative modeling.
- Supports versioning.
- Minimizes infrastructure costs (since it is a cloud platform).
- Supports any operational system (since it is web-based).
Cons:
- Minimal free version.
- Does not support popular relational databases like Oracle and IBM DB2.
- Expensive compared to other tools.
- Lacks syntax support.
- Best features available only in higher-priced versions.
- Auto-layout feature that works well only in small models.
2. MySQL Workbench
Developed exclusively to work with MySQL DBMS, it is one of the best-known and most used free tools for those who work with this DBMS, both for ER modeling and executing SQL scripts, as mentioned in this article.
Its main advantage is how easy it is to work with the MySQL DBMS. With a focus on physical modeling, it is the ideal tool for small and medium projects and for those starting or already have experience with database modeling.
The MySQL Workbench tool is offered in three main editions: Community, Standard, and Enterprise. The Community Edition is the base for both of the commercial editions, and Oracle explains on the tool's website that all improvements made to the basic framework of MySQL Workbench will be applied to the Community Edition.
It is worth noting that the tool has a plug-in mechanism that allows creating new extensions for the product. This means you can add new features to it based on the specific needs of each user or organization.
The commercial versions of the product include extra modules and plug-ins that make advanced capabilities available to its users. Reverse engineering and data synchronization especially merit highlighting (for more on reverse engineering, see this article). They allow the model to be synchronized directly with the MySQL database and vice versa. In the Community Edition, you can do this through Generating SQL Scripts.
Another important capability in the SE version is the validation of data models. Validation is extremely important in managing migrations between different versions of MySQL. In addition, there are capabilities for generating and printing model documentation in this version, in both HTML and text formats.
Pros:
- Has a free (Community) version.
- Generates scripts from the model.
- Allows reverse and forward engineering.
- Has syntax support.
- Has Windows, Linux, and MAC OS versions.
Cons:
- Only works for MySQL.
- Does not support collaboration.
- Some features available only in paid versions.
3. Astah Professional
Astah is well known to Java developers. This tool is currently available in two versions: Professional (paid) and Community (free).
It is a CASE (computer-aided software engineering) tool widely used for modeling software solutions using UML, with a language closer to human thought. It is also a handy tool for working with other types of diagrams such as use cases and class diagrams.
It natively includes the java.util and java.lang libraries (utility libraries used for defining attributes of UML elements). You can create codes in Java, C#, and C++ (Object-C) natively, based on class diagrams.
Astah works with the plug-in concept, making it possible to generate code for other programming languages like PHP when added to the tool. The created models are transformed into codes in a concept known in computing as forward engineering. It also transforms existing code into UML models through reverse engineering.
The tool allows you to generate the logical database model through an ER diagram. That is, it makes the Data Definition Language (DDL) of database tables based on the ER model (but only in the Professional version).
Astah is primarily focused on UML modeling. It is good for professionals already immersed in the Java world and do not want to migrate tools just to generate an ER diagram. However, despite being a handy tool for full-stack developers, it is missing the specialized capabilities of a professional ER modeling tool.
Consider it a quick fix if you want to do everything in the same software. If you have any questions about what you need to find in your tool, this article can help!
Pros:
- Has a free (Community) version.
- Generates scripts from the model.
- Allows multiple DBMSs (SQL-ANSI 92).
- Allows reverse and forward engineering.
- Has Windows, Linux, and MAC OS versions.
Cons:
- Is mainly focused on UML.
- Does not support collaboration.
- Has ER diagrams only in paid versions.
- Lacks syntax support.
4. SQL Power Architect
SQL Power Architect is a potent tool for working with data models. If you have a giant project, this is worth checking out. It's aimed at big projects and has a free version as well as three paid versions.
SQL Power Architect works with major DBMSs (PostgreSQL, Oracle, MS SQL, MySQL, and more). It does everything you need for ER modeling (in the paid version, of course).
Its main advantages are the excellent visual interface with ER and OLAP diagrams, access to databases through JDBC, simultaneous connections to multiple data sources, the comparison of data models with identification of differences, and the generation of synchronization scripts.
It also supports the "dragging-and-dropping" of the tables from the model to the playpen (diagram), forward engineering and reverse engineering, saving the data structure in a project that can be worked on remotely, supporting GPL licenses, etc. Finally, it has rudimentary ETL support for designing data warehouses.
Talking about database tools usually implies highlighting their limitations. It lacks support for views, triggers, check restrictions, and stored procedures – all essential capabilities for designing more complex databases. It does not support working in groups or controlling versions, and it is missing a web interface.
Pros:
- Has a very powerful free version.
- Generates scripts from the model.
- Allows multiple DBMSs (SQL-ANSI 92).
- Supports reverse and forward engineering.
- Has Windows, Linux, and MAC OS versions.
Cons:
- Supports only the basic structures (tables and keys, for example), even in the commercial versions.
- Does not support collaboration.
- Lacks syntax support.
- Has some features that are available only in paid versions.
5. Draw.io
Draw.io is a free online diagram and flowchart editor. This service allows users to create their processes with a straightforward layout. You can insert various shapes, texts, and arrows, and change colors and line thickness.
Two menu bars, one at the top and another on the side, provide quick editing tools that let you enhance your flowchart. The user can save the file as a PDF, JPEG, PNG, or SVG and sync it with external services like Google Drive, OneDrive, Dropbox, GitHub, and Trello. It is an easy-to-access web tool used to develop various diagrams (including, of course, the ER diagram).
It may not be as well-known as the other tools presented in this article. Still, it stands out precisely for its ease of use and access, it requires no downloads, and it can be used from any environment. The only requirement is access to the internet, so save this to your favorites – you may need it at some point in the future.
The key advantage of Draw.io is its usability. It is very simple and allows anyone to work on it without any previous knowledge. The organization of the elements by category in the side menu makes your work much faster.
The elements for building graphics make it an interesting application for those in need. Several sections are dedicated to items used in the construction of website structures (information architecture); this is a good tool for these professionals.
The main challenge with Draw.io is that you cannot work on it offline. Some more advanced features would be welcome, and the help menu is not sufficiently comprehensive to help you with how to get the most out of the tool.
Pros:
- Intuitive usability.
- Elements organized into categories.
- Works with layers and overlays.
- Has sections dedicated to information architecture.
- Supports any operating system (since it is web-based).
Cons:
- Cannot be offline to use.
- Includes only the basic capabilities for ER.
- Lacks syntax support.
- Has some features that are available only in paid versions.
6. Lucidchart
Lucidchart is an online editor specializing in professional flowcharts, organization charts, and diagrams. It works directly in your browser, so you don't need to install anything on your PC.
The registration is quick with an email address or with your credentials for Google, Yahoo, or Microsoft. There are many free layouts available, categorized into Business Analysis, Education, Mind Maps, among others.
After selecting the model, an editor will open so that you can modify the flowchart as you wish. You can insert texts, new variables, and arrows. You can change colors and edit the width/height and the fill of any shape. The editor has rulers and alignment grids for easy creation.
There's even a sharing feature for showing the file to your friends or co-workers for their review or approval. You can save the file for later editing or download as PDF, PNG, JPEG, or SVG.
Despite having all these features and being an excellent tool for flowcharts, Lucidchart, like Draw.io, is not an ER-specific tool. It can help if you need a generic tool, but it is missing the specific capabilities those who develop ER diagrams look for.
It is software almost exclusively for drawing diagrams – it is extremely competent for this purpose – but it lacks the capabilities a more robust ER tool would have. Unfortunately, Lucidchart will not meet your needs if you’re looking for a tool with code generation, syntax support, or reverse engineering.
Pros:
- Intuitive usability.
- Elements organized into categories.
- Works with layers and overlays.
- Has sections dedicated to information architecture.
Cons:
- Is very basic as an ER tool.
- Does not support online collaboration.
- Lacks syntax support.
- Has some features that are available only in paid versions.
7. Navicat
Navicat combines capabilities from several ER modeling tools. It supports most of the features used in modern database management systems, such as stored procedures, events, triggers, functions, views, etc.
Navicat allows you to efficiently transfer data across multiple database systems or into plain text files with the SQL format and encoding. Batch jobs for different types of databases can be scheduled to run at a specific time. Other features include an Import/Export Wizard, Query Builder, Report Builder, Data Synchronization, Backup, and Job Scheduler.
It is specific to ER modeling, but it has some cons that are not trivial. It does not have a free version which, on its own, makes the tool difficult to promote; it does, however, have a free trial version.
The second is its price: plans start at $29.99 for non-commercial use and at $64.99 for commercial use; the perpetual licenses are $599.00 and $1299.00, respectively.
Another drawback is that the best features are available only in the more expensive versions. And finally, it is system-installable software, with limitations in the operating systems where it can be used. This bucks the current trend that favors cloud-based web systems.
It's a good tool, but there are better ones on the market at lower prices or even for free.
Pros:
- Intuitive usability.
- Allows reverse/forward engineering.
- Works with layers and overlays.
- Supports multiple relational databases.
- Supports Windows, Linux, and Mac.
Cons:
- Less accessible pricing.
- Does not support collaboration.
- Lacks syntax support.
- Has some features that are available only in paid versions.
- Lacks a free version (free trial only).
8. Toad Data Modeler
Modeling a database requires a thorough understanding of the problem to be solved. The use of models is essential for a clearer vision and a formal representation of reality. To speed up creating and maintaining these models, we use CASE tools (computer-aided software engineering).
The Toad Data Modeler tool, formerly known as Case Studio, aims to help data modeling. We will explore the main features of the accessible version of this tool. Let's see what this commercial product can offer.
Among the tool’s key capabilities, besides the easy modeling and maintenance of the model, is the consistency test. It lets you evaluate the model even before the physical creation of the database. You can generate reports in HTML or RTF that serve as detailed model documentation. It also has the ability to export the model to an XML document or an image.
There are other features in the paid version of the tool. For example, reverse engineering allows you to generate the model from an existing database and control model versions.
The Toad Data Modeler does not have a free version; it only has a free trial. Its prices are not even published, which makes it difficult to get the tool if you need it quickly. It is also missing some capabilities like collaborative versioning and modeling, and it is installed on the user's machine and not web-based like the others seen above. In short, it is a tool with capabilities already available in others, but harder to get thanks to bureaucracy and pricing.
Pros:
- Intuitive usability.
- Allows reverse/forward engineering.
- Supports multiple database capabilities.
- Supports multiple relational databases.
- Supports Windows, Linux, and Mac.
Cons:
- Pricing not easily available.
- Not easy to buy.
- Does not support collaboration.
- Does not support versioning.
- Does not support model cloning.
- Lacks syntax support.
- Has some features that are available only in paid versions.
- Lacks a free version (free trial only).
9. Erwin Data Modeler
The Erwin Data Modeler is practical and straightforward. It enables better visualization, control, and database documentation.
We can design our database by defining the entities that make up the model with their respective attributes and relationships. You can determine the characteristics of these entities, making them real in one or more physical models. We can automatically create the model implemented in a database or generate scripts to implement the changes made to the model. We can synchronize two databases to guarantee an identical structure in both. It has several other capabilities.
This tool allows the user to work with data models in two ways. The first is more conventional: as physical Windows files, usually with the .ER1 extension.
The other involves a CA tool called AllFusion Model Manager. It is the repository for Erwin data models. This repository is created in a database, where an Erwin data model is saved in the form of records in the Model Manager tables.
Erwin dominated the market for many years, but it has been surpassed by newer tools such as Vertabelo and SQL DBM. In addition to not having a free version, its price has always been relatively high. This is because it had been the only one on the market for a long time and had always been sold to commercial enterprises.
Pros:
- Intuitive usability.
- Allows reverse/forward engineering.
- Supports multiple database capabilities.
- Supports multiple relational databases.
Cons:
- Price difficult to find.
- Only supports Windows.
- Does not support collaboration.
- Does not support versioning.
- Does not support model cloning.
- Lacks syntax support.
- Has some features that are available only in paid versions.
- Lacks a free version (free trial only).
10. Vertabelo
We saved the best for last!
Vertabelo is 100% web-based. You can work on your templates in Chrome or Safari. Vertabelo's graphical tools for designing database models are simply excellent! Intuitive and straightforward, yet very comprehensive and professional. There is no desktop license, download, or installation. You can work remotely from any computer connected to the web.
The biggest advantage of this tool is that you can share database models with your team and collaborate with others via a web browser. It’s free for smaller projects (up to 3 templates and 20 tables in each template) and has commercial versions for larger database projects.
Vertabelo is the most powerful tool of the ten described in this review. It has all the capabilities of the second-place tool on this list (SQL DBM) and more, such as a much more comprehensive free version, an auto-layout system that works very well, and the ability to create public links from the template, among others.
Its capabilities are so vast that the only thing I find missing from the tool is the ability to generate HTML documentation automatically. That said, I would consider that a nice-to-have.
Pros:
- Generates scripts from the model.
- Allows reverse and forward engineering.
- Supports collaborative modeling.
- Supports versioning.
- Minimizes infrastructure costs (since it is a cloud platform).
- Supports any operating system (since it is web-based).
- Has a powerful free version.
- Supports multiple relational databases.
- Extremely accessible pricing (even for students).
- Has syntax highlighting.
- Has a fully functional auto-layout feature.
- Generates public links for the model.
- Supports model cloning.
- Supports XML export and import.
Cons:
- Does not generate HTML documentation automatically.
Find the Best ER Diagram Tool for You!
I have used most of the tools presented here for database modeling. There is no doubt Vertabelo is the most powerful, so much so that I replaced three tools with Vertabelo – it met all the needs that were being met by three tools! Each of these tools has its value, but in working collaboratively, versioning, or even teaching classes – the three things I do a lot – Vertabelo has proven to be the best solution. See it for yourself here!
