Data Modeling Tools: Choosing the Best for Your Needs

Before you can choose the right data modeling tools, you have to understand what a data model is and how to use it. This article will explain those concepts and then compare some of the various data modeling tools available.

A data model is defined as an abstract model that organizes several aspects of data (e.g. data description and semantics and the consistency of data constraints). There are two types of data modeling techniques: the entity-relationship model (ER model) and Unified Modelling Language (UML).

For database administrators, business or data analysts, and data architects, the most important technique is ER modeling, which allows us to create ER diagrams. This is the model we’ll cover in this article; it emphasizes what data is needed and how it should be organized instead of what operations will be performed on the data. The ER diagram is like an architect's building plan – it helps build conceptual models and set the relationships between data items.

What Is Database Modeling?

A data model (or database model) determines the logical structure of a database and fundamentally guides how data can be stored, organized, and manipulated. The most popular example is the ER model, which describes the entities and their relationships. Based on this concept, database modeling defines the creation of the ER diagrams necessary to compose the database’s structure. Data modelling can be divided into three categories: conceptual, logical, and physical data models.

Conceptual Data Model

This data model defines what the system contains. It is typically created by data architects for business stakeholders. The purpose is to organize, scope, and define business concepts and rules. It’s an organized view of database concepts and relationships; the purpose of creating a conceptual data model is to establish entities, their attributes, and their relationships. At this level, hardly any details about the actual database structure are included.

The three main components at the conceptual level are entities, attributes, and relationships, and the characteristics are an organization-wide coverage of business concepts. Conceptual models are developed for a business audience and are designed without reference to hardware specifications (like data storage capacity and location) or software specifications (like the database management system vendor). The focus is to represent data as a user will see it in the "real world”. Conceptual data models known as domain models create a common vocabulary for all stakeholders by establishing basic concepts and scope.

As shown in the figure above, a conceptual data model should indicate relationships from a business perspective – for example, by modeling the relationship between the company and employee, which is generally a one (one company) to many (several employees) relationship. Below we have a diagram of a conceptual model for a library. Note how simple it is:

It is important to remember that conceptual modeling is independent of hardware or software, so there is some degree of freedom to describe the conceptual model regardless of the database management system (DBMS) that will later be used to implement it.

Logical Data Model

This model defines how the system should be implemented regardless of the DBMS. This model is typically created by data architects (with  business analysts’ help) to develop a technical map of rules and data structures. It is composed of entities, relationships, and attributes.  Each entity represents a set of persons, things, or concepts about which the business needs information. Each relationship represents an association between two entities.  Each attribute is a characteristic or piece of information that further describes an entity, as shown in the image below:

The above logical data model presented above represents a social network photo sharing platform; it’s used to define the structure of data elements and to set the relationships between them. The advantage of using a logical data model is that it provides a foundation for the physical model; however, the modeling structure remains generic.

Some of the characteristics of a logical data model are:

• It describes data needs for a single project.
• However, it can integrate with other logical data models based on the scope of the project.
• It’s designed and developed independently from the DBMS.
• Its data attributes are defined by data types with exact precisions and length.

One of the most important features of this data model is that normalization processes are typically applied until the model reaches the third normal form (3NF).

Physical Data Model

The physical data model describes how the database will be implemented using a specific DBMS. This model is typically created by database administrators and developers for the actual implementation of the database.

This model offers database abstraction and helps generate the schema; this happens because of the richness of the meta-data offered by a physical data model. This model also helps in visualizing database structure by replicating database column keys, constraints, indexes, triggers, and other relational database features. In the physical model of a database, the components of the bank’s physical structure are then detailed, which is important to understand in order to respect the limitations imposed by the chosen DBMS.

The figure below shows part of a database project for a biometric system. Despite similarities with the relational model, their differences (such as the description of the fields) are well presented.

The characteristics of this physical model are as follows:

• This model describes the data needed for a single project or application.
• However, it may be integrated with other physical data models based on project scope.
• It contains relationships between tables that address the cardinality and nullability of the relationships.
• It is developed for the specific DBMS version, location, data storage etc. to be used in the project.
• Its columns have exact data types, lengths, and default values (where applicable).
• Its tables have primary keys and the foreign keys, views, indexes, access profiles, authorizations, etc. are defined.

If you still have some doubts about how to create or modify the different types of data models, there are excellent resources that can help you.

Comparison of Database Modeling Tools

There are many database modeling tools on the market, but most of them have serious limitations, like running in a single operating system or not allowing model sharing and simultaneous collaboration. One of my questions has always been: What do I need in an ER diagram tool? There are Vertabelo articles that clarify these issues and that helped me make some comparisons. For your convenience, I’ve presented a comparison of some of the most popular database modeling tools below:

It’s easy to see that there are a lot of limitations, especially for those who work in large teams or who use operating systems other than Microsoft Windows. Even free tools cover only one scope (like just one database, for example), are single-user only, or do not allow versioning or model export in meta-languages like XML.

Vertabelo: A State-of-the-Art Data Modeling Tool

In twenty years of working with the market’s main databases, there were always doubts about the tools I was going to use (even more so because I am an avid Linux user), which eventually ended up generating incompatibility between projects developed over time.

Several times I found myself thinking "Is it so difficult to make a collaborative tool that can be used by multiple platforms?". I was still thinking about it when I came across the Vertabelo Database Modeler. My first move was a (skeptical) comparison with the other tools I had already used (as presented above); after several tests, I concluded that the volume of resources and the facility offered by Vertabelo was so great that it was worth carrying all my projects over to this tool. And even this portability was easy, since Vertabelo supports the reverse engineering, import, and export of data models; it took less time than I thought it would.

Initially, one of the features that most caught my attention was that the tool is web-based (compatible with any operating system and accessible from any computer) but still maintains the fluidity that we feel in a well-written application. The interface is intuitive and easy to understand, even entitling you to a "prize" if you finish the tutorial. Second, it is used by several representative companies in the market (Oracle, Indeed, Yelp, GoDaddy, among others) which (at least for me) reinforces what I already knew: that it provides resources in unique and easy to use combinations.

As can be seen in the Generate SQL Script screen, even the features that are common in other tools have additional features in Vertabelo: the generation of SQL scripts, for example, can be done for selected parts of the model; the program finds potential problems and suggests corrections (which greatly facilitates the process of implementing the physical database), and describes critical errors and warnings. This level of detail helps a lot when you’re building really big models.

What Database Modeling Tool Will You Choose?

There are several tools on the market, but many of them cover only part of your needs. As you become more professionally experienced, you need more resources. Vertabelo has the best of both worlds: it is easy enough to be used by beginners, and it has features that are only seen in some extremely professional (and expensive) tools – and exclusive features, too.

Working with a team, quickly and accurately correcting mistakes, importing and exporting SQL and XML files, and working with customers who have multiple databases are all common in the database world. And they are all addressed by Vertabelo, which I’ve chosen as my official tool and used to start rewriting my projects (again, without too many difficulties). I strongly recommend you check out this tool, as it covers all levels of knowledge – you’ll not need to spend time in the future rewriting your models in tools that have the resources you need. Vertabelo covers it all.