Complex database models are challenging to read and manage. In Vertabelo, you can use many different techniques, including color-coding tables and encapsulating groups of tables in subject areas. Doing this will increase the model’s readability, making it easier to navigate and search within it.
A complex data model can be organized using subject areas. Subject areas in Vertabelo are named rectangles that group tables based on a functional or business context. They help break the model into smaller, more readable chunks.
Although subject areas help make a model’s complexity more manageable, they can cause significant confusion when they overlap. When two subject areas overlap, it means entities inside the subject area are used and shared for different functionalities.
Having too many overlapping subject areas or references between different subject areas can induce noise in the model and prohibit you from using subject area entities for specific contexts. So, Vertabelo designed the shortcut tables feature to solve this problem.
The shortcut tables feature lets you create copies of a given table and place them in different subject areas.
In this article, you will learn how to organize ER diagrams using the shortcut tables feature.
What Are Shortcut Tables?
Simply put, shortcut tables are copies of a given table created to reduce complexity. This complexity is induced by extended and overlapping lines, often across multiple subject areas.
shortcut tables are most useful when several tables from one subject area are referencing tables from another subject area.
As shown in the image above, the regular tables are represented as solid rectangles. The shortcut tables have dotted boundaries (for example, the passenger table). When you have a dotted rectangle, it means that it is a copy of the table and that there are other copies of the table present in the ER diagram.
However, a solid rectangle means that it is the only copy of the table (for example, the baggage_check table). If you remove this table from the ER diagram, you will remove the table from the model.
On the other hand, if you remove a shortcut table, you only remove that copy of the table. The original table is still in the model.
Flight Booking & Management Example
To demonstrate shortcut tables, let’s look at an example ER model. Imagine a simple flight booking and management system in which the tables are divided into three subject areas: Booking & Boarding, Transport, and Security.
We are using a simple example, and it is not hard to organize data models of this size. However, most full-fledged databases have hundreds of tables. Dividing hundreds of tables into subject areas and creating a readable, coherent ER diagram is a significant challenge.
shortcut tables are very helpful for organizing ER diagrams in a better way. Now, let’s see how to add a shortcut table.
How To Add a Shortcut Table
As you can see in the Vertabelo data model, the three tables in the Security subject area are referencing the passenger table. This simplistic representation of the data model is used in an actual flight booking & management system.
The complete ER diagram will be much more complex and potentially have many more subject areas, leading to more reference line overlaps. To prevent this from happening, you can create shortcut tables for the tables referred to most frequently.
To create a shortcut table, go to the left panel menu and right-click the table name. Click the Add shortcut table option. Alternatively, you can use a shortcut,?Ctrl+K or Cmd+K. Take a look at an example of the former option in the screenshot below:
Shortcut Table Nomenclature
You can use shortcut tables with or without subject areas. Depending upon their use, the nomenclature of the shortcut table and the original table changes a bit.
For example, if there were no subject areas in our Vertabelo data model, a shortcut table to the passenger table would be named passenger:2. Vertabelo would label the original table passenger:1.
If your original and shortcut tables are enclosed in subject areas, rather than the number identifiers, a combination of table name and subject area is used as the table name: ?table_name:subject_area_name.
For highly complex ER models, the model can require more than one shortcut table in the same subject area. In this case, the format of the table name will be in this format: ?table_name:subject_area_name:numeric_identifier.
Reorganizing After Adding a Shortcut Table
After adding the shortcut table, you can get rid of the cross-subject-area reference lines to the passenger table. Instead of one passenger table, you now have two tables with the following names: ?passenger:Security and passenger:Booking & Boarding. Consequently, all the three tables in the Security subject area are directly referencing the passenger:Security table, as shown in the screenshot below:
Finding Shortcut Tables
Notice that both the original and shortcut tables are both dotted rectangles. This means that if you delete either one of them, the remaining one would become the primary table. The dotted lines would then convert to solid lines.
Just like you have the option to perform a Find in diagram search on a regular table, you can perform a similar search on tables with shortcuts. Vertabelo gives you the flexibility to focus on the exact version of the table you want.
In our case, we see two options: ?Find in Booking & Boarding and Find in Security. This is shown in the screenshot below:
Congratulations! You are now ready to use the shortcut tables feature in Vertabelo.
Share Your Thoughts
Data modeling is one of the most important aspects of data engineering. If you have well-designed and well-organized data models, the data becomes easier to consume, and consequently, more value is derived from the data.
Many of Vertabelo’s features are designed to make it easier for you to develop and organize ER diagrams. Shortcut tables are one of those many features.
We’d be happy to learn about your experiences in better organizing data models. Let us know in the comments section, or write to us at firstname.lastname@example.org.