Advances in web-based technologies, particularly HTML5, have made online data modeling a more credible option than ever. Tools have emerged that make it easy and efficient to design a database and generate the SQL code for a specific database product, offering a viable alternative to an on-premises solution, at least in certain circumstances.

Here I look at five tools that provide online modeling services, ranging from commercial products to free, open-source solutions. They include Vertabelo, GenMyModel, dbDiffo, WWW SQL Designer, and DbDesigner.net. Each one takes a different approach to delivering its services and completing basic tasks, such as adding tables, columns, or relationships. You’ll find that some tools are more feature-rich and user-friendly than others, but each one has its own advantages and charms.

That said, none of the tools provide the level of functionality you get with an advanced on-premises solution such as PowerDesigner or ER/Studio, but not everyone needs such an extensive set of features-or the price tag that goes with them. In fact, all five online tools come either completely free or have a free version available, making it possible to try all of them without having to commit one way or the other. Not surprisingly, the free versions associated with the commercial products have limitations on their use.

I selected these five tools based on those that seemed to be grabbing the most attention right now, taking into account that this is a market yet to fully catch on. For each tool, I’ve tried to be as accurate as possible with my details, but the documentation for many of the products is very poor-or virtually nonexistent-so I was left trying to make sense of what I could through a process of serendipitous discovery. In one case, for example, I had not realized the product I was working with was a commercial one until I accidentally stumbled upon the fact when trying to make sense of a feature that had nothing to do with pricing plans.

My choice of these five tools does not represent an endorsement of any one, although I do have my personal favorites. You should also be aware that there are other options out there. For example, Sea Quail offers a data modeling tool that in 2011 was described on Codeplex as a “proof of concept” (http://seaquail.codeplex.com). I cannot say whether the tool has been updated since then.

You’ll also find tools such as Ragic (https://www.ragic.com/), which provides an Excel-like interface for building an online database. Plus, a number of drawing services offer a database modeling component, such as Creately (Creately) and Lucidchart (Lucidchart).

For this article, I chose tools that were concerned primarily with data modeling or in which data modeling played a prominent role. For each one, I’ve tried to provide a good overview of how the tool works, but you will certainly want to dig into the tools more thoroughly if you’re serious about online data modeling. With that, let’s take a look at what I found.

Vertabelo

• Pricing: Free basic version; free academic version; advanced version available for a monthly subscription starting at $9/month or on a per-use basis starting at about $1/day; in-house installation starting at $1399 for a one-time license fee
• Supported DB products: Model and generate SQL for PostgreSQL 9.x, Oracle Database 11g, MySQL 5.5, SQLite 3.7.x, DB2 9.7, SQL Server 2012, HSQLDB 2.3; also export model as XML
• Supported browsers: Chrome, Safari (beta)
• Reverse engineering: Free tool available; can also import directly from an SQL or XML file
• URL: https://www.vertabelo.com

If there is one tool that stands out as my personal favorite, Vertabelo is it. The tool’s layout and usability suggest from the start that this is a product that has been carefully considered and planned. The intuitive design makes it easy to get started, letting you create a model and add the necessary objects within minutes. Although that can be said about all the tools I looked at, Vertabelo offers a particularly professional looking interface with most operations very streamlined and efficient, while providing features not available in other products, such as the ability to add schemas to the table definitions.

Even so, there were a couple spots that caused momentary uncertainty. For example, when I created my first character column, I found an nvarchar option and an nvarchar(%) option and wasn’t sure which to use, but with a little trial-and-error, I was able to figure out how to proceed with this column and all the others. I ran into the same issue when adding a column to a unique index. I had missed a step that could have been made clearer, but once again, I figured it out with relative ease.

What helped me resolve the last issue is that Vertabelo, by default, validates the model and provides ongoing feedback, so if something is not right, you know right away. Vertabelo also provides more and better documentation than I’ve seen with other products, which can often come in handy, despite what documentation naysayers would have you believe. For example, when I created the AuthBook table shown in the preceding figure, I was taking the wrong approach to adding the foreign keys. But I quickly tracked down the information I needed in the documentation and figured out what to do.

Vertabelo comes with plenty of features for creating a fairly complete model. For example, you can add indexes and check constraints to a table or include SQL statements with your table definition that run before or after the table is created. You can also create views or add notes to the model. In addition, the design surface itself is very user-friendly, allowing you to easily resize and reposition tables and access various elements within the design, as well as clearly understand the structure of your tables and their relationships.

GenMyModel

• Pricing: Free basic version; more advanced version starting at $4/month; professional version that includes all features starting at $19.20/month for one-to-five users
• Supported DB products: Product-neutral modeling; generic SQL generator that requires customization; sample SQL generator for PostgreSQL; export as XML Metadata Interchange (XMI)
• Supported browsers: Internet Explorer, Chrome, Firefox, Safari, Opera
• Reverse engineering: Appears to be no way to import SQL, but can import Visio 2013 models
• URL: http://www.genmymodel.com/database-diagram-online

GenMyModel provides a platform for designing software architecture and business processes. Included in the platform is a tool for creating database diagrams. The tool is not as feature-rich as Vertabelo, but it comes with an intuitive interface that makes it simple to add tables and their relationships. An especially nice feature is how easy it is to configure a column. You need only select the column after adding it and then choose whether it is part of the primary key or should be unique or nullable. You can also define a default value or add a check constraint at the same time.

The tool’s features are fairly basic, however, and the free version is limited to creating only 20 objects in a model, which apparently counts not only the tables, but also each column and relationship. For example, model shown in the preceding diagram accounts for 16 of the 20 permitted items, which means you’re not going to be able to do very much with the free version, other than poke around a bit. Compare that to the free version of Vertabelo, which lets you create up to 20 tables, regardless of the number of columns.

Even so, the eloquent simplicity of the GenMyModel interface makes it worth serious consideration, especially if you’re interested in the entire GenMyModel platform. Tables snap to the design grid like a professional drawing tool and are painless to size and position. Column information is easy to access and understand. And the relationships between tables are clear and concise.

One interesting feature about GenMyModel is the Generator, which provides the mechanisms necessary to export the model to SQL (and other formats). The Generator uses Java Persistence API (JPA) annotations to serve as an intermediary step when translating the database model to SQL. This creates more work initially because you must create the generator necessary to translate the model to your specific database system (unless you use the sample PostgreSQL generator template), but this approach can theoretically give you more control over the SQL output, as long as you have the know-how to make it work. For some data architects and database developers, this approach will likely be more than they want to contend with.

dbDiffo

• Pricing: Free
• Supported DB products: Model and generate SQL for DB2 10.5, SQL Server 2008, MySQL 5.6 and 5.7, Oracle 11g
• Supported browsers: “certain internet browsers”
• Reverse engineering: Free tool available
• URL: https://dbdiffo.com

Not surprisingly, as we move from the commercial products to the free modeling tools, we find fewer features than we do with products such as Vertabelo or GenMyModel, but dbDiffo still delivers a good basic modeling tool, letting you generate tables and their columns with relative ease. You can set nullability, define default values, and create unique indexes. You can even create foreign key relationships with a simple drag-and-drop operation.

Although the dbDiffo interface isn’t as polished as Vertabelo or GenMyModel, there’s something very likeable about it, in a cartoonish sort of way. With a name like dbDiffo, a little whimsy seems appropriate. Besides, dbDiffo makes it quite easy to generate SQL for your database product, not only for the data model as a whole, but also for individual components, a feature I have not seen elsewhere.

Some of the SQL-generation capabilities are related to the model’s history. The tool continuously document’s all changes to the model, allowing you to view a snapshot of your work at any point of time simply by repositioning the slider in the History window. You can also roll back to that step, essentially deleting all subsequent steps.

The unique part of all this is that dbDiffo lets you generate a change script based on any step in the history. For example, if you add a column to a table, the operation is added as a step in the History window. You can then generate a SQL script that provides the ALTER TABLE statement used to define that column. This also applies to steps such as creating tables or adding indexes. In addition, dbDiffo lets you generate a SQL script specific to an individual table or column directly from the model.

The dbDiffo product might not be as feature-rich and user-friendly as its commercial counterparts, and building a complex model could be a bit cumbersome, but dbDiffo has an appeal all of its own, and you certainly can’t beat the price.

WWW SQL Designer

• Pricing: Free and code is downloadable through GitHub
• Supported DB products: Model for MySQL, SQLite, SQL Server, PostgreSQL, Oracle, CUBRID; generate SQL for MySQL
• Supported browsers: Firefox, SeaMonkey, Internet Explorer (6, 7, 8), Safari, Opera
• Reverse engineering: Might be able to import SQL from a MySQL database
• URL: https://github.com/ondras/wwwsqldesigner

WWW SQL Designer is a freely available modeling tool that you can access through GitHub, where you can also download the source code if you want to customize the app to make it your own. This is a very rudimentary tool that lets you do the basics. You can create tables, add columns, specify the nullability, and define a default. You can also define primary keys, foreign key, and indexes, but you can’t do much else. You can, however, create models that are product-specific and then easily generate the SQL code.

The interface itself is fairly basic as well. The columns are color-coded based on data type, rather than listing the type name, which means you need to memorize what those colors to understand how each column is configured. You also cannot tell which columns are the primary keys without digging into the column properties. In general, the interface is not as intuitive as they ones we’ve looked at so far and the various tasks can take some getting used to. This likely would not be the tool to use for a database of any size.

The biggest advantage to SQL Designer is its open-source nature. If you’re the type of person who likes to get into the nitty-gritty of the code and play around to see what you can make happen, you might find this to be a fun solution to explore and customize. JavaScript and PHP appear to make up the bulk of the source files.

You might also appreciate that fact that the author makes a demo available online so those interested in the tool can take it for a test drive before doing anything else. It’s a free solution, after all, so you have little to lose by trying it out. Just don’t expect the same UI experience you’ll get with the other products.

DbDesigner.net

• Pricing: Free basic version; paid version starting at $50/year
• Supported DB products: Model and generate SQL for MySQL, SQLite, SQL Server, PostgreSQL, Oracle
• Supported browsers: Not specified
• Reverse engineering: Import SQL from MySQL (feature described as “experimental”)
• URL: http://dbdsgnr.appspot.com

DbDesigner.net is a clean, basic tool, with a user-friendly interface that takes a no-nonsense, straightforward approach to data modeling. You add tables, you define columns, you create relationships.

Each column’s configuration settings are controlled through a single dialog box, which you access by clicking the editing button to the right of the column name. There you specify the name and data type, along with a length or default value, when appropriate. You can also specify whether the column is part of the primary key, nullable, unique, or a foreign key. If it is a foreign key, you specify the target table and column here as well.

Keep in mind, however, that this is the beta version of the product, which means you’ll run into the occasional issue as you go along. For example, when you first create a table, naming that table requires a couple extra steps that seem counter-intuitive. Notice also in the preceding figure that the database type indicates MySQL, even though the model is set for SQL Server.

That said, I didn’t find either of these issues to be a big deal, and I certainly appreciated the clean, simple interface. What did concern me, however, is what I ran into when generating the SQL code for the model shown above. It turns out that the code was missing a couple items. For example, the AuthBook table should have contained a composite primary key based on both columns, but the SQL created the primary key on only the AuthID column. In addition, the SQL included only one of the two foreign keys on that table.

Despite these issues, I still liked this product and believe that, once it gets past beta, it will be one worth going back to, especially if the developers add a few more features, such as being able to define check constraints. More documentation would be nice as well, but I could say that of most products.