Back to articles list
Do you find it challenging to choose the perfect ER diagram tool to save time and cost for your next database project? Unsure which features are the most important to include in your evaluation criteria? Read this article!
The entity relationship diagram, or ER diagram, has been an industry standard used in database modeling among database architects for many years. An ER diagram is a graphical way of presenting a proposed database schema (tables, references, views, etc.) using standard graphical objects.
There are many online and offline ER diagram modeling tools available in the market, with many features to support database architects. The purpose of an ER Diagram tool is to provide a work environment with a user interface, features, and capabilities to draw ER diagrams at three different levels (conceptual, logical, and physical) of database modeling. The quality of an ER diagram tool depends on different factors such as the user-friendliness of the work environment, available features and capabilities, the supported database engines, etc.
Let’s discuss the most important features your ER diagram tool should have to be the perfect one for your next project.
Does It Draw an ER Diagram?
Many diagramming tools are available in the market, supporting a variety of diagrams such as ER diagrams, UML diagrams, flow charts, workflow diagrams, etc. But when you select an ER diagram tool, you should check whether it supports modeling all types of database objects such as tables, both one-to-one (1:1) and one-to-many (1:M) references, views, sequences, procedures, and triggers. If it doesn’t, you will draw an incomplete data model, and there will be manual work to create the physical database.
As an example, let’s take a look at the Vertabelo data modeler (an online ER diagram tool). The Vertabelo data modeler has the required features to draw complete ER diagrams for all three levels of data modeling (conceptual, logical, and physical). Shown below is a logical ER diagram for an online shopping app created in the Vertabelo data modeler. As you can see in the toolbar, it has the capabilities for adding entities and their attributes with identifiers, other properties, and all types of references (including crow’s foot notation). Read more about the symbols used in the Vertabelo data modeler here.
As shown in the example below of the Vertabelo physical data modeler, it has the features to support database objects, tables, references, sequences, and views.
Read more on generating a physical diagram from a logical diagram in Vertabelo.
Does it Generate SQL DDL Files from ER Diagrams?
This is a key feature of any ER diagram tool. If your ER diagram tool has the modern capability of generating SQL DDL files from your ER diagram (physical data model) to create your physical database, it will save you significant time and cost for your business. You should consider this as a key factor in choosing the perfect ER diagram tool.
For example, the Vertabelo data modeler allows you to create an SQL script from a physical diagram.
As you can see in the image above, you can generate an SQL script from your physical diagram in the Vertabelo data modeler to:
- Create all elements in your database.
- Remove all elements from your database.
- Create or remove only selected elements.
Read more on SQL Generation here.
What Database Engines Does it Support?
When you choose an ER diagram tool, it is important that it supports your database engine. This means that your ER diagram tool should be able to create SQL DDL files for the physical database creation in all of the popular database engines.
It should understand those database engines to validate your data model according to the constraints and other requirements of each engine (we will discuss this further in the next section). There is no point in having other advanced features otherwise, because you would have to do most of the tasks related to your database manually, which increases the project duration and cost.
Also, your tool should support not only your current database engine but all popular database engines. Then you don’t have to worry about any future changes and migrations. Your data modeler will take care of them and will save considerable costs.
As of today, the Vertabelo data modeler supports these popular modern database engines.
Does it Understand the Database Engine?
A perfect ER diagram tool should understand the supported database engines. This means it should know the data types in the engine and the constraints of the supported databases. In other words, your ER diagram tool should be able to validate your data model according to the selected database engine. For example, your ER diagram tool should check that your table and column names are in the correct formats and within the limits of the selected database engine, that the table has a primary key (PK), that both sides of each reference have matching types, etc. Your ER diagram tool should have this capability to get the real benefit of supporting many database engines and other features. If it doesn’t, you will have to spend many hours manually checking and correcting after creating the physical database and increasing the cost of your project.
As you can see below, the Vertabelo modeler validates your physical diagram and shows the problems under “Problems” in the work environment.
If you need to ignore them, you have the option to disable each validation under “Model validation settings” in the “Model Properties” panel shown below.
Does it Have a way to Import an Existing Database?
As a database architect, you generally do not start every project from scratch. You will have to work on upgrading and making many changes to existing projects. In order to do this, it’s important to have a way to import an existing database into the tool. Modern ER diagram tools have the “reverse engineering” feature to import an existing database. Then you can make any change to the respective database schema using the tool.
For instance, Vertabelo offers a feature to import an existing database in three ways:
- Live connection to the database: You can download the Vertabelo Reverse Engineering Tool and connect to the running database using its graphical user interface (GUI) or command-line interface (CLI). It will read your database schema (tables, references, views, etc.), and you can download an XML file to your local folder. Then you can create the physical data model of your database in the Vertabelo online ER diagram tool by uploading the XML file.
- Import from SQL: If you have generated an SQL file with the CREATE statement of your database, you can upload the file to the Vertabelo ER diagram tool online as explained here.
- Import a Vertabelo XML file: If the older database was created in Vertabelo, you can import the Vertabelo XML file with the model definition.
Does it Support All Levels of Database Modeling?
We often go through three levels of database modeling to develop our physical database:
- Conceptual data modeling.
- Logical data modeling.
- Physical data modeling.
When we choose an ER diagram tool, we should choose one that can be used from the beginning to the end of our data modeling process. In other words, our ER diagram tool should support drawing these three data models. Otherwise, we will waste time and money to use different tools at different levels during the modeling process.
The Vertabelo online ER diagram tool provides a perfect solution for this.
As you can see above in the popup screen from the Vertabelo data modeler, you can create your conceptual diagram and the logical diagram using the “Logical data model” option and the physical diagram using the “Physical data model” option.
You also don’t have to start each level from scratch in Vertabelo. Once you create your conceptual diagram, you can upgrade it to a logical one. After that, you can create your physical diagram from your logical diagram according to your database engine. After modifying and finalizing your physical diagram, you can generate an SQL script that can be used to create your physical database. This feature saves you hours and a lot of cost.
Read more on creating all three data models and physical databases in the Vertabelo tool here.
Does it Support Easy Collaboration?
This is one of the most important features, not only in an ER diagram tool but also in other software tools and applications, today more than ever due to the global pandemic situation. Most of us are working from home these days, and we expect to access our tools conveniently and collaborate easily with our team from wherever we are.
With the Vertabelo data modeler, you can share your data models by simply providing the email addresses of your team members and clicking a button to specify one of the three different access levels: Owner, Editor, or Viewer. With the options to avoid any conflicts, everyone who shares the model can work at the same time. Also, there is an option to share your data model with your customers who do not have a Vertabelo account by emailing a public link.
You can read more on collaboration in Vertabelo here.
Are You Ready to Evaluate Your ER Diagram Tool?
Although there are many modern ER diagram tools available in the market today, it is especially important to understand the most critical factors to look for so that you can save a lot of time and money.
Your tool should have the features and capabilities to draw complete ER diagrams. It should create your physical database from a physical data model. It should support and understand all popular database engines. It should validate your data models according to the selected database engine. It should have features like reverse engineering to create a data model from existing physical databases. It should support conceptual, logical, and physical data modeling, and online ER diagram tools are perfect for your collaborative work. The Vertabelo ER diagram tool does all of these.
We will be happy to hear about your experiences with different ER diagram tools and features. Share your thoughts in the comments below.
