Back to articles list
Still spending hours of manual work on MySQL data modeling? Discover the best MySQL modeling tools that save you time and money!
The ER diagram (or entity-relationship diagram) has become an extremely popular data modeling concept among data architects, database administrators, and DB developers. And MySQL is one of the most popular database management systems (DBMS), especially for web applications. Thus, many ER diagram tools that support MySQL (aka MySQL ERD tools) have arrived in the market during the last decade. However, it can be challenging to choose the tool that best suits your needs. So, in this article, we’ll compare various MySQL ER diagram tools and determine which is the best choice for you.
1. Vertabelo
Vertabelo is an online design tool that supports all major DBMSs, including MySQL. It offers several automated features for generating a MySQL physical database as well as moving through Conceptual, Logical and Physical models with less manual work. Its key MySQL data modeling features include:
Generating Physical Data Models from Logical Models
Vertabelo automates the process of creating a MySQL physical data model from a logical data model.
The above images show the steps of this process. We’ve also published a step-by-step guide to generating a physical data model from a logical data model in Vertabelo.
Validating MySQL Data Models
Vertabelo understands MySQL’s unique parameters; for example, it knows MySQL’s data types and other constraints. The design tool validates your generated physical model according to MySQL rules.
As you can see in the above physical model, Vertabelo checks if your table and column names are in the correct format, if tables have primary keys and meet MySQL’s reference requirements, and more.
Generating MySQL DDL Files
One of Vertabelo’s key features is its automated DDL file generation from ER diagrams (physical data models). It can generate a MySQL script to create all the elements in a database or remove some (or all) elements from a database, as shown below.
You can learn more about SQL Generation in Vertabelo here.
Importing Existing MySQL Databases
Vertabelo allows you to import MySQL and other supported databases into the tool and modify them in three ways:
- Via live connection to the database using the Vertabelo Reverse Engineering Tool.
- Uploading a SQL file containing your database’s CREATE() statements to the Vertabelo data modeler, as explained here.
- Import XML files with the model definition into the Vertabelo modeler.
Other features that Vertabelo offers those looking for an online MySQL ER diagram tool including options for remote collaboration, version control, and supporting views, subject areas, text notes within data models. Because Vertabelo supports many DBMSs including MySQL, it gives database architects the flexibility to create databases that are portable among different DBMSs and platforms.
2. MySQL Workbench
MySQL Workbench is a unified visual tool that can be used by database architects, developers, database administrators (DBAs), and students. This tool is specifically designed for MySQL and is available on Windows, Mac OS X, and Linux. It’s a MySQL modeling tool, but it also provides functionalities like SQL development and administration tools for server configuration, backup, user administration, etc.
As a MySQL tool, MySQL Workbench allows you to design and model your databases visually as well as to generate and manage them.
Designing Database Schemas
MySQL Workbench has all the required notations to draw an entity-relationship diagram and design your database from the conceptual level to the physical data model.
Specializing in MySQL
As the name suggests, MySQL Workbench is designed especially for MySQL. It comes with model and schema validation utilities to ensure that you use best-practice standards for data modeling and to help you to build an error-free MySQL physical diagram.
Forward and Reverse Engineering
MySQL Workbench‘s forward engineering feature helps you generate a MySQL database using your physical data model. The SQL code (i.e. the DDL script) needed to create the database in MySQL Server is generated automatically. Also, this tool has a reverse engineering feature that lets you build a data model from an existing MySQL database. It can also generate data models using SQL scripts.
Managing Changes
MySQL Workbench helps DBAs and developers manage the complex and difficult database change processes involving different versions of database schemas. Using its Schema Synchronization and Comparison utilities, a DBA can compare two databases or a database and a model and visually see the differences.
MySQL Workbench has many features that make DBAs’, data architects’ and developers’ lives easier. However, because it’s only meant for MySQL, it doesn’t support the creation of portable data models and databases through different DBMSs and platforms.
3. Navicat
The Navicat data modeler supports MySQL as well as other DBMSs like MariaDB, Oracle, SQL Server, PostgreSQL, and SQLite. Like other data modeling tools, this one also provides many features for modeling data and creating a physical database.
Modeling Data
The Navicat data modeler is a powerful design tool that helps data architects create conceptual, logical, and physical data models. You can use Navicat to create complex ER diagrams in one of three standard notations: Crow’s Foot, UML, and IDEF1x. This is a simple and user-friendly tool that can be used to develop a complete data model. It offers automated creation of logical and physical relational diagrams from your conceptual diagram.
Reverse Engineering
Like the other MySQL modeling tools we discussed, Navicat also provides reverse engineering to load existing database structures and to create ERDs. It also visualizes the data models.
Generating SQL Code
Using the Export SQL feature in Navicat, you can generate SQL scripts for individual parts of your data model, including comments, referential integrity rules, and more.
Navicat also provides features to model your data throughout all levels or to automate logical and physical data models from your conceptual model, re-engineered database, or SQL generation script. Also, you can create portable data models and databases that can be deployed among many DBMSs and platforms. This is a multi-connection database development tool.
4. Visual Paradigm
Visual Paradigm is an online modeling platform that supports various models, including data models. It also supports a range of database management systems, including MySQL, Oracle, SQL Server, Sybase, PostgreSQL, SQLite, HSQL, MariaDB, Derby, Informix, Firebird, IBM DB2, FrontBase, H2, and Amazon Redshift.
Modeling Data
Visual Paradigm provided all the required notations to model all three levels of data models. It also provides a feature called Table Record Editor that lets you enter sample records for ERD entities. These sample records help readers to understand the format of the data that will be inserted. The Database View Editor allows you to edit the database view.
Visual Paradigm lets you derive logical and physical data models from (respectively) conceptual and logical models with its automated feature. Also, this design tool helps you to sync between ER models.
Generating a Database from an ERD
Like the other tools that we discussed, Visual Paradigm also generates DDL files from the physical data model to create the physical database. You can create selected entities or the entire ERD.
Also, this tool has a feature to patch revisions made in the data model into the database. The patch tool analyzes the database and generates the required script to create the changes.
Reverse Engineering
This MySQL ER diagram tool helps data architects and developers reverse engineer MySQL and other supported databases and edit them visually.
Visual Paradigm comes with the most common key features like data modelling and reverse engineering. It also has some different features like patching data model changes to the database. These features save time and money in database projects. Also, this tool supports collaborative work.
5. SqlDBM
SqlDBM is an online and collaborative modeling tool that supports many DBMSs, including MySQL, SQL Server, Snowflake, Microsoft Azure, and Amazon Redshift. Its MySQL modeling tool comes with features that enable you to design and develop new databases and revise existing databases.
Data Modeling
The SqlDBM modeling tool has all the required features to model your database, either from an existing DB or from scratch. You can also use it to edit an existing data model.
Reverse and Forward Engineering
This tool’s reverse engineering feature reads and imports the DDL script automatically and generates the data model. The tool does not access login credentials or underlying data.
The forward engineering feature allows you to generate a SQL script of the database model created in SqlDBM. To do that, you simply click Save and then click Generate SQL.
Altering Scripts
This data modeler’s Alter Script feature synchronizes changes made to the data model with the physical database.
Revisions and Documentation
The Compare Revisions feature helps you to identify the differences between your SqlDBM revisions. The tool’s DB Documentation helps you document a description for each database schema object and element (table, relationship, column, key, etc.)
Find the Best MySQL ER Diagram Tool
We’ve looked at a number of MySQL data modelling tools. You’ve probably noticed that most come with forward and reverse engineering, data modeling notations, and various change management features. Also, many are online tools and have great features for collaborative work. A perfect data modeling tool lets you focus on modeling only the concept. Seventy to eighty percent of the rest of the process has been automated, saving you precious time (and maybe some frustration, too).
What data modeling tool is right for you? To find the answer, think about the tool which provides the above features in a way that’s easy for you to use. Each tool has its own details and special features, and it's often these that will make your decision clear. If cross-platform portability matters, look for a data modeler that supports lots of DBMSs. If you’re often pressed for time, look at the level of automation and how user-friendly each tool is – the gentler the learning curve, the faster you’ll be able to use this tool.
Overall, my recommendation is to try a few tools out if you can and then make your decision. Choosing the right tool will make a big difference in your work.
