A neat, well-organized database diagram is more than just eye candy. It can also increase team productivity by facilitating the understanding of a data model and reducing meeting and training times.
A database diagram serves multiple functions. On one hand, it is the foundation for creating the structure of an operational database in a database management system. On the other hand, it is a communication, documentation, and guidance tool that must endure and evolve over time.
When using an entity-relationship diagram as documentation or guidance, readability and clarity are paramount. In a small ERD (one with a dozen entities or less), legibility and clarity are easily achieved. But in a more extensive ER diagram, the visual organization of the elements is vital to ensure their readability and correct interpretation.
In this article, I share some tips to improve the layout of your database diagrams so that they are always easily readable and understandable. This will benefit your future self (you’ll understand the diagrams better when you have to work with them again in a few years) and other developers, who can use them without having to ask you for clarifications. You may want to first check out some cool Vertabelo features and learn how to organize your ER diagram. If you’re new to Vertabelo, I also recommend you learn how to get the most out of Vertabelo model editor features.
Need some help with general database schema design concepts? Read this detailed guide to database schema design to get started.
Ok, on to the schema readability tips and tricks.
Tips for a Better Database Diagram Layout
1. Use a Naming Convention
The first point to keep in mind to improve readability is to adopt a consistent naming convention – and respect it when naming all objects in the diagram. It’s a good idea to make the naming convention explicit in the diagram layout by detailing it in a text note. (See #7 for other uses of text notes.)
When you don’t use a proper naming convention, you can run into something like this:
The lack of a consistent naming convention hampers the readability of this model.
In the above example, you can’t understand the logic of the data model without someone to explain the function of each table and column. The situation changes radically if you apply a basic naming convention:
Using a naming convention makes the model much easier to understand. You don’t even need to change the diagram layout.
By applying a simple naming convention and using meaningful names, the model becomes instantly understandable to anyone who reads it.
2a. Build from Scratch Rather Than an Existing Database
Building a database diagram from scratch is ideal for an optimal diagram layout. Starting from a blank sheet of paper, you can design your ERD so that it grows in a neat and tidy way, taking care to keep it easily readable according to the criteria detailed throughout this article.
But often you have to create an ER diagram for a database that already exists. You can do this by drawing its tables and relationships one by one, but it will be a very tedious and error-prone task. Ideally, you should use a data modeling tool that allows you to generate a diagram from an SQL script, i.e. by reverse engineering the database. You can do this with Vertabelo and get a physical ER diagram that accurately reproduces the structure of your database.
2b. Check Reverse Engineered Database Diagrams
If your reverse engineered database model contains lots of tables, the resulting diagram layout is likely to be very large and difficult to read. Although your reverse-engineering tool does its best to generate everything as neatly as possible, only your expert designer’s eye will know how to improve its layout to make it clearer and more readable. Let’s take a look at some simple ways to declutter your model.
3. Find the Hearts of the Model
In every database schema, there are certain tables that have a large number of relationships with other tables. You can easily identify them because they have many lines running through them, like a microchip in the center of an integrated circuit. These are the main tables –the “hearts” of your schema.
If you find more than one heart in your schema, it is an indication that you can divide the schema into subject areas. This will improve its readability by leaving each area with a single main table. For example, in the following schema you can see that the
application tables are both connected to a large number of other tables:
In this diagram, job and application are candidates to be the “hearts” of two subject areas.
For optimal readability, you should place the main table in the center of the diagram or subject area. In this way, the viewer's attention will naturally be drawn to the heart of the diagram and the interpretation of the model's functionality will be more evident.
The two subject areas are named after this model’s two main tables (
application). This improves the layout.
4. Minimize Line Lengths, Overlaps, and Concealment
The lines that represent relationships between entities define much of the functionality and usability of the model; it is vital that they are clear and easy to follow. When distributing objects in a large diagram, lines commonly become very long, cross each other, or go under an entity/table.
To improve the visibility of relationships, you should:
- Minimize the number of relationships that cross subject areas. Ideally, subject areas should be highly cohesive and as decoupled as possible. This means that there should not be many relationships between tables belonging to different subject areas. If there are, you may need to rethink and redefine the subject areas. Or you can use table shortcuts (see below).
- Bring related entities as close together as possible. Look for tables with only one or two relationships and bring them as close as possible to their related tables. (I think of this process like unraveling a ball of yarn; you start with the ends that stick out.) In this way, you will shorten line lengths and avoid unnecessary overlaps. When moving tables, be careful not to leave them covering any relationship lines.
- Work on inner tables once you’ve moved outer tables closer to their related peers. To do this, you may want to identify clusters of tables: groups of two or three tables that have a common function. Give all the tables in a cluster a common color that identifies them. (See below for other uses of colors in Vertabelo.) Arrange the tables so that those in one cluster are close to each other and there is no overlap between their relationships. When you need to move these tables, select all the tables in the cluster and move them as a unit.
5. Use Colors to Differentiate Groups of Tables
Vertabelo offers a wide color palette that allows you to ‘paint’ the tables in your diagram. You may think that this is merely an aesthetic issue to make your work look good. But the use of colors to differentiate groups of tables allows the reader to easily identify tables that belong to a certain business area.
In large diagrams, colors greatly simplify the identification of differentiated groups of tables.
Learn how to color ERD diagrams in Vertabelo to see how you can greatly improve the readability of your database diagram with just a few clicks.
6. Take Advantage of Table Shortcuts
Another way to prevent too many relationship lines cluttering up your diagram is to use table shortcuts (a unique feature of Vertabelo). When there is a relationship between two tables that are far apart in your diagram, you can create a shortcut to one of the two tables and place it next to the related table.
When creating a shortcut, both the original table and the shortcut are drawn with dotted lines. It is important to note that the existence of a shortcut does not change the structure of the underlying database in any way – it only duplicates the object visually.
You can also use shortcuts to avoid having relationship lines that cross between different subject areas. When you have one table related to another in a different subject area, instead of drawing a line from one subject area to the other, you can create a shortcut from one of the two tables and take it to the subject area of the other table. For more information on shortcuts, read about Vertabelo’s shortcut tables and how to use them effectively.
7. Don’t Forget Text Notes
As we saw above, text notes are very useful for adding information to a diagram. In addition to documenting design criteria – such as the use of a particular naming convention – text notes serve to guide the reader and save them the trouble of asking the diagram’s creator for explanations.
In the following example, a text note details the functionality of a column that would otherwise require a specific explanation:
Text notes allow information to be added about aspects of the diagram and design decisions that are not explicit in the model.
There are other uses of text notes in data modeling that could also improve your database diagram layouts.
Better Database Diagram Readability Equals Higher Productivity
Face-to-face meetings are something worth encouraging in a work team, since they promote synergy among teammates. But they are a waste of time when they must be held for a team member to explain things that could very well be documented.
In database design, an easily readable diagram can save many hours of work in explaining and understanding a data model. The tips I shared in this article can consistently improve the layout of your database diagrams and consequently generate significant increases in your team’s productivity.