Data Model Design Tool Open Source

When developers approach databases they often neglect proper data modeling. This can result in a lot of fiddling with the data structure over time. Fiddling is fine for a proof of concept. But when tasked with developing a complex and evolving project, you want to define the data structure from the start.

Moreover, when there is a disconnect between the development team and the business team, a lot of potentially useful data can be lost, misplaced, or underused. Can data modeling bridge this gap between DBAs and business? And what tools should you use to achieve that?

What is data modeling?

A data model is a visual representation of data specification. Data models are often designed in the form of a UML diagram. It doesn't matter if it is an SQL or NoSQL database; data can be stored and collected in many ways. The process of defining what data needs to be collected and how it should be stored is data modeling.

Data modeling can be done with any UML editor or even pen and paper, but there are a plethora of tools specifically designed for data modeling. Data modeling tools worth their salt will not only draw pretty pictures but also integrate directly with your databases.

But before we get to our list of top ten data modeling tools you should know, let's quickly recap how data modeling works.

Conceptual model vs Logical model vs Physical model

Data modeling usually contains three layers:

The Conceptual Model – to establish the entities, their attributes, and their relationships
The Logical Data Model – to define the structure of the data elements and set the relationships between them
The Physical Data Model – to describe the database-specific implementation of the data model

There is a lot more to be said about the different layers of data modeling, and I recommend some further reading.

The benefits of data modeling

There are many benefits to data modeling. For starters, the simple process of thinking about what data is required before defining the data structures already improves the quality of data. However, the benefits don't stop there.

Business-oriented teams and people can model the conceptual layer from a business perspective. By using a conceptual layer, non-technical people that will eventually use the data can define what kind of data they need. The logical layer can be done by IT, and only the physical layer defined by developers. Having the data model be scrutinized from different perspectives naturally improves the quality of data.

Furthermore, proper data modeling allows visualization of the data structures. This can help identify weaknesses and strengths in advance. It can also allow developers to choose the correct data structures for the data to be stored in. This prevents the need of migrating data from one place to another when the data structure is found to be inadequate.

What are data modeling tools?

Data modeling tools enable quick and efficient data modeling. They bridge the gap between the various layers of data models and the data itself. Most data modeling tools will generate database schemas or DTD automatically, allow merging and comparing schemas and models, and reverse engineer existing databases into models.

Good data modeling tools will be accessible to non-technical people to allow conceptual data modeling with ease.

Top 10 data modeling tools for 2021

1. erwin Data Modeler

The erwin Data Modeler has been around for a good 30 years. If nothing else, erwin is sure to understand data and data modeling. This data modeling tool takes it a step further than simply designing the data model and ensures constant integration with databases such as MySQL and PostgreSQL to visualize your data and get the most of it.

Multiple versions to suit the customer's specific needs
Robust comparison tools
Rich visualization with metadata

erwin offered several versions with key differences. The standard edition offers model creation and deployment. The navigator is a read-only version to help visualize data. The workshop edition is a repository-based solution meant for collaboration. And the most specific tool is the NoSQL version, which, as its name suggests, handles non-relational databases.

Both the standard version and the workshop version offer comparison tools that can be used to find differences across multiple databases or versions.

Pricing: $299 a month or $2,999 a year for the standard edition
$449 a month or $4,499 a year for the workshop edition

2. ER/Studio

ER/Studio, not unlike erwin, has been around for a while, and it shows both in the positive and the negative. While ER/Studio provides a robust feature set that comes from decades of improvements, they sometimes have difficulty keeping up with new technologies.

Git integration along with merge/comparison tools
Business first mindset
Forward and Reverse engineering capabilities

The Git integration is modern and effective, using SSIS and SSRS standards for ease of use.

ER/Studio was designed specifically with the philosophy of bridging the gaps between business and developers, to get the best out of your data.

Whether you already have data or you're starting fresh, ER/Studio will provide the tools you need to make the best use out of it going forward. The tool will also help you reduce redundancies.

Pricing: Starts at $1,470.40 per user (workstation), but prices are generally negotiated.

3. DbSchema

DbSchema is a visual database designer & manager that supports a wide variety of database types including SQL, NoSQL, and Cloud databases.

Among others, DbSchema offers:

Support not only for GIT but also Mercurial, SVN, and CVS
Frequent bug fixes and platform updates (every 2 or 3 months)
Built-in random data generator

Unfortunately, DbSchema does not provide enough details on the description of the fields and lacks version management features. Besides, users report that it is not as reliable as other tools.

Pricing: Starting at $63 for one user (for academic purposes) and $127 for individual users for a perpetual lifetime license. A free version is available, as well as evaluation licenses upon request.

4. ERBuilder

ERBuilder Data Modeler is aimed at making data modeling accessible for developers. This means that it is not a good fit for conceptual and logical data modeling layers.

Easy to use visual data modeler
Forward and reverse engineering of databases
Data exploration

Unfortunately, ERBuilder lacks collaborative and versioning control tools. But the robust, easy-to-use graphical interface is a strong point in its favor. While exploring data with ERBuilder you will find it easy to navigate between different tables and automatically build detailed diagrams at the click of a button.

Price: Free version available, and a wide variety of pricing options from $49 subscription model and $99 perpetual model.

5. HeidiSQL

HeidiSQL is an open-source and free physical layer data modeling tool designed for ease of use. Being free, HeidiSQL is one of the most popular tools for MariaDB and MySQL worldwide.

Unlike proprietary competitors, it doesn't have any noteworthy singular features. That said, it does have some stability issues, although users report it has no adverse effects and simply requires a restart.

Pricing: Free

6. Navicat Data Modeler

Navicat Data Modeler is an affordable and powerful data modeling tool, with a very pleasing user interface. Data modeling tools often look outdated, but not Navicat.

Like many of the higher-priced data modeling tools (like erwin and ER/Studio) Navicat features:

Reverse engineering tools
Physical as well as Conceptual and Logical modeling
Navicat Cloud

Navicat's pricing model makes it much more affordable than erwin and ER/Studio and does not fall short in its set of features, although some users report lacking field descriptions.

The Navicat cloud supports synchronizing your connection settings, queries, and models across devices including Windows, macOS, and iOS devices.

Pricing: Starting at 22.99 Monthly.

7. Toad Data Modeler

Toad Data Modeler supports a wide range of platforms and also many different versions. For example, Toad supports MS SQL Server as far back as 2000.

Rapid deployment
Logical and physical layers

Toad setup and licensing are on the more complex side and can be improved. It also requires a different application to work separately on Oracle and MySQL. A one-stop solution would be preferable.

Piercing: Starts at $293 annually.

8. Archi – Open Source ArchiMate Modelling

Archi is a Data Modeling toolkit designed for all levels of models, including business enterprises and small companies. It uses ArchiMate, a visual notation language that helps explain complex systems.

Open-source
Welcoming website and user guide
Clean and accessible roadmap and version history

Unlike HeidiSQL, an alternative open-source solution, Archi has a clean user interface and allows conceptual as well as physical data modeling.

Pricing: Free

9. DeZign for Databases

DeZign for Databases is an intuitive and easy-to-use tool for data modeling designed for developers and DBAs.

Multiple display modes
Pan and zoom window
Advanced features like N:M-relationships

DeZign is a tool that focuses on data viewing features and complex data modeling. Targeting developers and DBAs means that it lacks the tools for effective conceptual later modeling.

Pricing: Starting at $228

10. SQL Database Modeler

SQL Database modeler is a slick and modern SaaS, being completely web-based. The tool has many singular cloud features and collaborative tools and is very easy to get into. No download or installation is required.

Web application
Easy to start and get a first impression
Code-free development

Honestly, the SQL Database Modeler website is such a pleasure I urge you to visit it and explore it yourself. If nothing else, you'd enjoy the tour. The quality of the website speaks to the quality of the tool itself, as both are web-based. That said, a major appeal of the platform is its code-free approach wherever possible.

Pricing: Starting at $25 monthly or $240 annually.

Although the list is by no means exhaustive, it should help you get started in choosing the solution for you. The next step would be to list your requirements and compare the solutions side-by-side.

When looking at which tool to use, you'd be right to make sure that the tool supports the databases you intend to use, as well as all the layers you require. Whether you intend to collaborate or not, version control is a great boon to any piece of software.

Only a few of the solutions on this list feature cloud support; the modern SQL Database Modeler and Navicat Data Modeler stand out in this regard. However, there is security in going with a tool as well established as erwin Data Modeler or ER/Studio.