Best Tools To Design Database

Best Database design tools for Forward and Reverse Engineering

This article highlights the basic requirements for visual tools meant for the forward and reverse database engineering. We also name the most popular visual solutions for database design.

Basic Requirements for Visual Tools of Data Design

The first and foremost thing that a database design software must ensure are forward and reverse engineering. One way to achieve it is visual design. It generates code when changes are introduced into the data model, supporting forward engineering. And, it builds a graphical model from code, supporting reverse engineering.

Therefore, the visual solution for database design must provide graphical forward and reverse engineering in the following capabilities:

Visualize database structure for further analysis.
Cluster logically related objects using containers.
Track logical correlations between tables.
Open large SQL database diagrams.
Create and edit database objects in a diagram.

Also, there should be the following instruments for visualization:

The list of notations on data modeling syntax.
Objects' commenting.
The documented plan, showing the logical structure of an SQL diagram at present. You can use it when navigating through tables and their dependencies in a database diagram.
The diagram overview (scaling and navigating through a diagram).
The level of detail for the displayed data (whether to show constraints, indexes, etc.).
The list of designs (optimization for black and white printing, color printing, etc.).

Database visual solutions may also involve other requirements.

Overvie w of Visual Tools for Database Design

After we have clarified the essential criteria of database visual design tools, let's review the most popular software solutions. We've compiled this list from various Web ratings and reviews, paying particular attention to forward and reverse engineering options present in those tools.

MySQL Workbench

MySQL WorkBench is a powerful toolset for database architects, developers, and designers. Among the other features, it provides options for visual database design and managing databases. There are tools for both forward and reverse engineering. Users can transform graphical data models into databases on MySQL Server smoothly and error-free. The software is suitable for data modeling, server configuration, database administration, and backup. Moreover, the product is compatible with Windows, Mac, and Linux.

Image 1. The data model in MySQL Workbench

DbForge Studio

dbForge Studio for MySQL is a multi-functional solution for the creation and maintenance of MySQL and MariaDB databases. The functionality covers PL/SQL coding, data synchronization, table analysis, schema comparison, and many more options for all types of database development jobs. It is worth mentioning the database structure visualization feature — it displays all objects and their properties in MySQL database diagrams. The tool for making such diagrams is a part of the database designer for MySQL. This way, you can do reverse engineering with drag-and-drop. The product is optimized for team collaboration.

Image 2. The data model in DbForge Studio

SSMS

SQL Server Management Studio (SSMS) is a Windows-based free utility for any SQL infrastructure. This solution lets the developers access, manage, and develop all SQL Server, SQL Data Warehouse, and Azure SQL Database components. The environment provides a wide variety of graphical tools and rich script editors suitable for administrators of all levels. One of the advantages is working with databases and data warehouses both on local computers and in the cloud. Note that the solution is compatible with OS Windows only (AMD or Intel).

Image 3. The data model in SSMS

Visual Studio

Microsoft Visual Studio is a multi-purpose IDE (integrated development environment) that is suitable for database development and maintenance. It can import existing schemas and convert them into databases. With the help of compilers, graphical designers, and many other features, it can perform all the necessary tasks related to the database design. Reverse engineering is available, and the software can make T-SQL files for each particular database object. Visual Studio is compatible with both Microsoft SQL Server databases and Azure SQL databases.

Image 4. The data model in Visual Studio

SQLGate

SQLGate is an integrated solution for database development and management. Its main goal is to simplify all types of operations with databases. It supports all popular databases and allows the users to connect these databases directly. This integrated software does not require complex installations, and all its functions are simpler. The object explorer ensures quick access to object panels for analysis and editing. The debugging utility helps detect any errors in making queries and thus helps to write queries correctly.

Image 5. The data model in SQLGate

Navicat Data Modeler

Navicat Data Modeler is a popular database design tool with the main scope of building data models (conceptual, logical, and physical). The solution is suitable for the visual design of database structures, reverse and forward engineering, and many other tasks. Users can create diagrams from database structures and visualize models to review the relations of elements. Any part of the model can be converted into a physical database entity. All popular databases are supported.

Image 6. The data model in Navicat Data Modeler

DBeaver

DBeaver is a free tool for database developers, administrators, and analysts, compatible with all popular databases. Based on the open-sources framework, DBeaver allows developers to create and add program extensions, enhancing functionality. Among other features, the tool provides the option of viewing the database structure in the ERD form. The diagrams show the database objects and their dependencies for analysis. It is possible to edit the layout by drag-and-dropping the elements, but the diagram only presents the database's current state. It is impossible to save changes.

Image 7. The data model in DBeaver

ER/Studio

ER/Studio is a solution for database design and data architecture. It supports most database platforms and is favored by data specialists in many fields. Its robust functionality of data modeling makes it useful for both logical and physical database design. There are such options as automated database code generation, data modeling, XML Schema generation from either the logical or physical models, comparing the model to the actual database, etc.

Image 8. The data model in ER/Studio

Among other popular tools, it is worth to mention the following ones:

PowerDesigner
Toad Data Modeler
TablePlus
Valentina Studio
DbSchema
ERBuilder
SchemaSpy
DbDesigner
SchemaCrawler
Erwin Data Modeler
Visual Paradigm ERD Tools
RapidMiner
MapBusinessOnline
Vertabelo
Lucidchart
SQL Database Modeler
HeidiSQL
Apache Spark

In our work, we find DbForge Studio for SQL Server the most helpful and convenient. We will further describe how we create the Database Diagram Tool using this tool and how we work with these means applied to reverse engineering.

Major Engineering Features of Database Diagram in DbForge Studio for SQL Server

Earlier, we considered forward and reverse engineering in the article dedicated to Database Reverse Engineering (link) and presented the most popular visual tools for database design. Now, let's proceed and examine the functionality and the tasks' performance of such a tool. For this, we've selected the Database Diagram Tool of the in DbForge Studio for SQL Server.

The first step in the process is to create a schema for the JobEmplDB database.

Go to the necessary database and select "Database Diagram" in the "Database" main menu:

Image 9. Creating a database diagram

Hold the left mouse button and drag the database to the "database diagram" area:

Image 10. Database diagram

Now we have a diagram of the JobEmplDB database. It shows all objects (tables, views, stored procedures). Moreover, it presents the dependencies between the tables.

Each table displays a block of constraints and a block of indexes. Double-click on a particular dependency, and it opens the Relationship editor:

Image 11. Relationship editor

Double-clicking on the table opens the Table editor:

Image 12. Table editor

The Table editor includes:

The content and definition of columns.
The content and definitions of constraints.
The content and definitions of indexes.
The content and definitions of statistics.
The content and definitions of triggers.
The definitions of the table on the T-SQL tab.

What is more, you can view the content of the table by going to the Data tab.

There is also a similar View editor:

Image 13. View editor

The View editor includes:

The definition of a view.

The graphic editor for the view definition.

The list and definitions of indexes.

The list and definitions of statistics.

The list and definitions of triggers.

Similarly, you can view the contents of the table if you go to the Data tab.

As for stored procedures and functions, the editor allows changing their definitions too:

Image 14. Editor of a stored procedure/function

All changes introduced into the database model by editors, the database saves itself. Therefore, you can modify the database graphically. It facilitates both forward and reverse engineering.

Alongside this, the Database Diagram tool allows the following:

1) Create visual containers and group objects if there are too many separate items:

Image 15. Grouping objects through a visual container

2) Comment on each element of a particular object, or that object itself.

3) Create additional graphic elements for a better perception of a database diagram:

Image 16. Creating a graphic element (note)

Image 17. Creating a stamp

4) Optimize the schema for printing:

Image 18. Optimizing the schema to print it

5) Track the logical correlations between tables:

Image 19. Tracking logical correlations between tables

Conclusion

As an example of a convenient and functional solution for both forward and reverse engineering, we have chosen the Database Diagram tool in DbForge Studio for SQL Server. This tool provides all the options necessary for efficient work. It has many advantages, but one of the most helpful things is that it makes it unnecessary to write queries (including complex queries) against system views for getting information on objects in the SQL Server database.

References:

1) https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/catalog-views-transact-sql?view=sql-server-ver15

2) https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-ver15

3) https://blog.devart.com/sql-database-design-basics-with-example.html;

https://blog.devart.com/generate-test-data-with-sql-data-generator.html;

https://blog.devart.com/export-and-import-json-data-via-dbforge-data-pump-for-sql-server.html

4) Database Diagram