Choosing the right open source data modeling tool is difficult, mainly because Google Search gives you a bunch of regurgitated pabulum.
And most started with something like this:
“Data modeling is the most crucial step in the database design process. It involves building a visual representation of abstract data models. These models, typically built around business needs, help define and organize the structure of data and the relationship between entities, and they can be either conceptual, logical, or physical.”
It’s not necessarily bad, but it’s certainly not what you’re looking for.
This is why we simply go ask our data team and practitioners for their opinions, here are the list of open source modeling tools that our data practitioners suggest.
But to make sure that this blog post reached you on Google Search, we’ll start with the basics.
I. How To Choose The Right Data Modeling Tools
With many companies scrambling to build a modern data stack, the demand for data modeling tools continues to rise. These tools simplify the usually complex modeling process by creating easy-to-understand visuals while minimizing human error within a much shorter time.
Here are some things to look for when considering a data modeling tool for your business:
01. Purpose: Aligning business requirements and standards with the tool is the first step to choosing the right modeling tool. As expected, modeling tools are different, and they all vary in terms of complexity. It is crucial to choose one that is extensive enough to serve all purposes.
02. Features: After shortlisting tools that align with business needs, the next step is to look at the features. Some offer comparatively more comprehensive services that make the modeling process even less complicated. Some useful features that you may want to consider are:
- Collaborative tools to facilitate working with team members on the same project
- Reverse engineering - importing SQL scripts from existing databases
- Forward engineering - the ability to generate code from the data model
- Support for various database types
- The integration of other tools
- Version control
- Exporting diagrams in different formats
03. Ease of Use: Choosing a tool with all the right features does not matter if it is too complicated to use. A data modeling tool will be more efficient if the user has the technical skills to use it. Ideally, it should be easy to use for all levels of expertise. All processes - installation, setup, automation of tasks, and the ability to make changes quickly - should be considered in this step.
04. Scaleability: You want to make sure that the tool you choose is capable of growing as your business grows. It should be able to accommodate growth in data needs, number of models, different database types, and collaboration.
05. Integration: It is also necessary for the tool you choose to easily integrate with other platforms and database types - relational and non-relational databases. The data model that this tool creates should also be in a format that is compatible with other software. These integration requirements all depend on the business needs and data infrastructure.
06. User Community: Apart from reliable customer support, almost every good business tool has a community where users discuss problems, solutions, and updates. When you need help with your chosen modeling tool, you should have an active community that can assist.
II. Top 14 Free and Open-source Data Modeling Tools
While there are many fantastic modeling tools like the Erwin data modeler and SqlDBM, they are not free. We will be reviewing some free and open-source tools that are equally good.
This online database diagram designer is mainly for people with technical skills - developers and data analysts. Its intuitive code-based interface makes it easy for users to draw Entity-Relationship (ER) diagrams. This tool is free for up to 10 visuals, and after that, there is a $9/month charge.
On the code editor on the left, users can write SQL queries to generate tables. They can then drag and drop the table to the desired position in the ER diagram. Dbdiagram also has a toolbar that makes sharing possible. Users can easily share ER diagrams online with anybody and export them to images and PDF files. They can also import existing PostgreSQL and MySQL scripts and create ER diagrams easily.
What Users say: Reviews for this tool are predominantly positive, with its beautiful visualizations being the most liked feature. Full disclosure: Holistics.io makes this, and we're proud of it!
This free and open-source software is one of the most popular data modeling tools for MariaDB and MySQL worldwide. It also supports MS SQL, PostgreSQL, and SQLite database systems.
Some features are: Connecting to servers via command line, scheduling events, generating SQL exports, exporting directly from one database or server to another, bulk editing, an SQL syntax editor, exporting tables in various formats, and managing user privileges.
What Users Say: Reviews from popular review sites indicate that people generally love how convenient HeidiSQL is to use, especially the direct server to server exports feature. However, it has many complaints of being unstable and crashing very often.
It is an open-source conceptual and physical data modeling tool that uses the ArchiMate modeling language. This language supports the analysis and visualization of various complex database systems.
It has a clean interface for creating views and a hints view that describes relationships to you if you get stuck. Its visualizer displays model elements in a radial-tree diagram that is easy to update. Version history, a sketch view for drafting model designs, and a canvas modeling toolkit for customizing reusable templates are some of its other features. This open-source data modeling tool works on Windows, Mac, and Linux.
What Users Say: Although Archi is not very popular, G2 reviews show that users love are its minimalistic interface, its ability to automatically suggest relationships between objects, ease of use, and the export to excel capability. Common criticism for Archi is often directed at its clumsy directory setup which makes finding certain things sometimes harder.
A Ph.D. student at UC Irvine developed this open-source Unified Modeling Language (UML) modeling tool in 1998. The UML is a general-purpose modeling language used to visualize the design of object-oriented software systems. ArgoUML supports all UML 1.4 diagrams.
The tool has an extended module - DB-UML - that offers more diagramming options for relational database schemas. ArgoUML, which is available in 10 different languages including German and French, requires minimal setup and runs directly in your browser. It also has other features - exporting diagrams in various formats, advanced diagram editing, reverse engineering, and generating SQL create statements from a model.
A particularly noteworthy feature is its built-in tool for design review and suggestions.
What Users Say: A common criticism of this tool, which users find very inconvenient, is its lack of an undo feature. Without an undo button, you’d have to save every change in a project or start over once you make a mistake.
PgModeler is an open-source database modeler that supports multiple PostgreSQL databases.
With its intuitive interface, it serves not only as a modeling tool but also provides a functional database server administration module. It supports GNU/Linux, Windows, and macOS.
This data modeling tool has a command-line interface to automate processes and allows model export in five formats. It supports reverse engineering by creating models of existing databases, while also offers constant model validation to minimize errors in a model. Other features include the ability to customize most of its features and automatic restoration of unfinished work in the event of unexpected quit.
What Users Say: While users think it is generally an incredible project, PgModeler is known to crash unexpectedly. As a partial solution to this problem, the latest releases come with a crash handler that generates reports for users to find and fix the bugs that caused the crash.
06. MySQL Workbench
MySQL Workbench is more than just a visual database design tool; it also integrates database administration, performance monitoring, and database migration. This free tool works with only MySQL Community Edition.
It has features like ER Diagramming, a drag-and-drop modeling interface, reverse engineering from existing databases, and generating SQL scripts from models. Its advanced SQL editor offers code completion, SSH connection, and Unicode support options.
MySQL Workbench has a lot of community support within the MySQL community, and it is now the second most downloaded product from the MySQL website. In addition to this, users also like the readily available customer support.
What Users Say: Some users frown upon its unintuitive interface which has too many cluttered features making it difficult to use. MySQL workbench is reported to be unstable occasionally and cannot handle large CSV imports sometimes.
Umbrello is a free and open-source tool for creating and editing UML diagrams, and it is available for Linux, Windows, and macOS.
It can create most UML standard diagrams, and most of its features depend on UML standards. Users can either import them from code in about seven different languages or build them and generate the code in Umbrello. Its user interface has a work area for editing and viewing model diagrams. These diagrams are created as XML Metadata Interchange (XMI) files and can be grouped based on user preference.
What Users Say: Umbrello users like its ease of use and the ability to generate code from UML class diagrams.
Open ModelSphere is an open-source UML modeling tool that supports all forms of data models - conceptual, logical, and physical. It allows for the conversion of models from one type to another.
Like many other data modeling tools, it has reverse engineering capabilities. It also supports forward engineering - generating SQL scripts from models and integrating them with an existing database. This tool works with all database management systems and offers special plug-ins for some of them.
What Users Say: Most users find Open ModelSphere to be a good modeling tool, although it is not as slick as other tools. It’s also reported to be hard to set up as it relies on a particular version of Java.
Like some of the tools mentioned earlier, DBDesigner 4 integrates database design, data modeling, and maintenance in one environment. This open-source visual database design system was primarily designed for MySQL database but has now been succeeded by MySQL Workbench.
Features include Model-To-Database synchronization, reverse engineering, SQL query builder, SQL command history, ability to save models within a database, and exporting output as an image. Version control is available but only when using database storage.
Database Deployment Manager (DDM) is an open-source database design tool that allows users - typically programmers - to create models and diagrams. It is also a database management software that enables users to create and maintain databases and create ER diagrams between tables.
DDM supports reverse engineering, has a unique visual query builder, and generates documentation for all activities. Other features include diagram export in different formats, database design validation, and version history.
DBDesigner is an online database modeling tool that allows users to design database schema without writing any SQL code. Its simple and intuitive user interface has features that simplify the modeling process. DBDesigner is currently available in over 26 languages that users can choose from a drop-down menu. It has a free plan that offers the creation of two models with ten tables per model.
This modeling tool, like many others, supports reverse engineering but only for MySQL, PostgreSQL, and Oracle databases at the moment. It also has forward engineering capabilities that allow users to create SQL scripts from scratch. The user interface has several keyboard shortcuts, instant saves, commenting, and other one-click options. Its collaboration feature with three different access levels allows team members to work on the same model at the same time. There is also the option to create public links to share projects with other interested parties.
What Users Say: Users can generate SQL scripts for MySQL, MSSQL, PostgreSQL, Oracle, and SQLite databases and export schema diagrams in PDF or PNG formats. All these features are included in both free and paid plans. DBDesigner users seem to like the product well enough.
Oracle SQL Developer Data Modeler is a free and powerful database design tool that can connect to any supported Oracle Database. It provides a platform for its users to work with different model types, and it can be used in local and cloud environments.
Its features include reverse and forward engineering, DDL code generation, ER Diagram creation, model formatting and validation, and in-built reporting. Its open-source source code management tool - Subversion - facilitates version control and collaborative capabilities by providing multi-user access to the same project at the same time.
The data modeler is hardly reviewed separately, being an extension of the SQL Developer.
DbSchema is a database management and design tool that supports multiple database types and allows users to create logical and physical model designs. It has both free and pro editions; however, DbSchema free has fewer features than the paid version.
The free version offers reverse engineering capabilities, an intuitive interface for creating schema diagrams, and an SQL editor with text auto-completion. For the Pro edition, users enjoy several additional features. Some of them are the ability to generate interactive HTML documentation, offline model designing, visual query builder, collaborative capabilities, and customizable database reports.
What Users Say: DbSchema has reviews and 5-star ratings on popular online technology marketplaces - Capterra and G2. Most users advocate for its smart query builder and comprehensive documentation. However, as it is not yet as popular as other tools, new users might find it hard to find an active user community.
This data modeling tool is tailored specifically for developers to create ER Diagrams for (physical) database designs. ERBuilder supports many database platforms but only functions on Windows Operating Systems.
Although it has paid options, it also offers a free version for educational and other non-commercial uses.
The free edition includes many noteworthy features such as an intuitive user interface, data model validation, exporting models as images, support for Relational and Crow's Foot notation, syntax highlighting, HTML model documentation, and support for some databases.
Reverse engineering, collaboration and version control with Git, database synchronization, and populating databases with test data are some features that are exclusive to the paid editions.
What Users Say: This data modeling tool has fifteen largely positive reviews and an average rating of 4.2 on Capterra, while on G2, it has a 3.5 rating. In particular, users like its robust reverse engineering capability and automatic documentation creation. The most recurring complaint on both platforms is installation difficulties.
Knowing which Data Modeling tool to use is just the first step, as we always say, adoption of effective business analytics is a people, process, and tools (PPT) problem. Companies that focus on tools alone don’t get very far.
This is why we wrote extensively about data modeling and its practice in modern data stack. Bill Inmon's Building the Data Warehouse was published in 1990, Ralph Kimball's Data Warehouse Toolkit was published in 1996, and Agile Data Warehouse Design in 2011. All of these books were published before the rise of the powerful, cloud-based, massively-parallel-processing columnar data warehouses. Our belief is that these powerful data warehouses enable vastly different modeling workflows from the past.
We spent a lot of time tracing the history of these ideas, and then canvassing the industry for new adaptations or modifications to them:
What's happening in the BI world?
Join 15k+ people to get insights from BI practitioners around the globe. In your inbox. Every week. Learn more
No spam, ever. We respect your email privacy. Unsubscribe anytime.