Data modeling is the process of analyzing the things of interest to your organization and how these things are related to each other. The data modeling process results in the discovery and documentation of the data resources of your business. Data modeling asks the question “What?” instead of the more common data-processing question “How?”
As data professionals, it is important that we understand what the data is and what it means before we attempt to build databases and applications using the data. Even with today’s modern infrastructure that includes databases with flexible schemas that are applied when read (instead of the more traditional method of applying the schema on write), you still need a schema and an understanding of the data in order to do anything useful with it. And that means a model of the data.
The modeling process requires three phases and types of models: conceptual, logical and physical. A conceptual data model is generally more abstract and less detailed than a complete logical data model. It depicts a high-level, business-oriented view of information. The logical data model consists of fully normalized entities with all attributes defined. Furthermore, the domain or data type of each attribute must be defined. A logical data model provides an in-depth description of the data independent of any physical database manifestations. The physical data model transforms the logical model into a physical implementation using a specific DBMS product such as Oracle, MySQL or SQL Server.
Navicat Data Modeler
Which brings us to the primary focus of today’s blog post: Navicat Data Modeler. We have looked at other Navicat products in this blog before (1, 2, 3), but those were performance and DBA tools. Navicat Data Modeler is designed to be used by data architects and modelers (but it can, of course, be used by DBAs, too).
A good data modeling tool provides the user with an easy-to-use palette for creating data models, and Navicat Data Modeler succeeds in this area. Navicat Data Modeler provides a rich interface for visually designing and building conceptual, logical and physical data models. Figure 1 shows a portion of larger logical data model for a university application.
Figure 1. A Logical Data Model in Navicat Data Modeler
The interface enables the user to clearly see the relationships, entities, and attributes at a high level, as well as the ability to zoom in to see details (see Figure 2).
Figure 2. Attribute details for the Student entity
The tool offers a lot of flexibility, so you can create, modify, and design models in a user-friendly manner and the way you like. Navicat Data Modeler supports three standard notations: Crow’s Foot, IDEF1x and UML.
Although easy to use, Navicat Data Modeler is a powerful data modeling and database design tool. As already mentioned, it supports conceptual, logical, and physical modeling. Importantly, though, the tool manages migration of models using reverse and forward engineering processes. Using the Model Conversion feature, you can convert a conceptual model into a logical model, modify and further design at the logical level, and then convert into a physical database implementation. Navicat Data Modeler supports MySQL, MariaDB, Oracle, Microsoft SQL Server, PostgreSQL, and SQLite. See Figure 3.
Figure 3. Forward engineering to a target database
OK, so that covers forward engineering, but what about reverse engineering? You can use Navicat Data Modeler to reverse engineer a physical database structure into a physical model, thereby enabling you to visualize the database to see the physical attributes (tables, columns, indexes, RI, and other objects) and how they relate to each other without showing any actual data.
Furthermore, you can import models from ODBC data sources, print models to files, and compare and synchronize databases and models. The Synchronize to Database function can be used to discover all database differences. You can view the differences and generate a synchronization script to update the destination database to make it identical to your model. And there are settings that can be used to customize how comparison and synchronization works between environments.
It is also worth noting that Navicat Data Modeler is fully integrated with Navicat Cloud. This makes sharing models much easier. You can sync your model files and virtual groups to the cloud for a real-time access at anytime and anywhere.
A proper database design cannot be thrown together quickly by novices. Data professionals require domain and design knowledge and powerful tools to implement their vision. Navicat Data Modeler offers one such tool that is worthy of your consideration.