Back to articles list
Have you finished preparing your logical data model in Vertabelo? Awesome! In this article, we'll show you how to generate the physical data model from the logical model in Vertabelo. It’s just a few clicks away. Ready? Let's dive into it.
Quick Intro
In this article, we'll deal with a slightly modified version of Microsoft's Northwind Database. We often use it in our LearnSQL courses, such as Customer Behavior Analysis in SQL. This diagram is based on the physical data model generated by our reverse engineering tool.
The logical data model looks like this:
How to Generate A Physical Model From a Logical Model
Converting a logical model into a physical data model takes seconds with Vertabelo. Start by clicking on the model name, which opens the main menu. Then click on the Generate physical model option:
A pop-up customization menu will appear:
Now you can change the name of the future physical model if you want. Remember to select the target database engine. You may pick from:
- PostgreSQL
- IBM DB2
- Oracle Database
- Microsoft SQL Server
- MySQL
- HSQLDB
- SQLite
- Amazon Redshift
- BigQuery
The Generate sequences and Copy text notes checkboxes are pretty self-explanatory, but I'll mention them later in this article.
When you're ready, click on the 
button. After a moment, a new tab will be opened and you'll be able to work with the physical model.
Your model is now saved in your Drive. Note that each model has a different icon – the same you see when you create a new document in Vertabelo.
Physical Data Model
Vertabelo has generated this physical model for me. As you can see, the layout has stayed the same:
Before we delve into each part of the new physical model, we need to verify that there are no problems:
We’re good. If problems do arise, click on each error/warning and resolve the issue with that element before proceeding.
How Elements Are Generated
Let's examine each element in detail. First, one more tip: element colors (and the rest of the formatting) in the logical model will be transferred to the physical model; so will comments, text notes (if the Copy text notes checkbox is selected when you generate the model), subject areas, and most names. So I recommend organizing your work before the conversion – it’ll make understanding the new model easier.
It’s good to note that Vertabelo will switch off the "Fixed size" option for some elements (as it will add or modify contents and some things could get cropped out and lost).
Okay, now on to the elements.
Entities → Tables
Each entity is changed into a table. The name of the entity becomes the name of the table, and any comments are copied. The data type of each attribute is changed to the matching type in our selected database. You can see the rules here. Primary identifiers are changed into primary keys, and attributes that are non-mandatory become nullable. Let's take a look at how the orders entity has changed.
As you can see, the generation process has added a column referring to the customer's ID; this is because there is a reference between the orders and customers entities in the logical model. The pattern here is:
{table of the referenced table}_{primary identifier of the referred table}
You’d probably want to change the name of the column to customer_id or something similar. You can do that in the Properties panel.
Additionally, if an entity in a logical model has non-primary identifiers, they will also be added to the generated table as alternative keys with the name {table name}_ak_{n} where n is the subsequent number for the next alternative key of that table.
Relationships → References
Most of relationships are converted to the respective references: one-to-one is changed to one to one, one-to-many is changed to one to many, etc. However, there are a few special cases that don’t have an equivalent in the physical data model. We'll explain how Vertabelo treats each of these special relationships in the next few sections.
Many-to-Many Relationships → 0-* + table + *-0
I didn't want to complicate the Northwind database with an example of such a relationship, so let's just leave Northwind for a while and use books and authors as an example.
One book can have multiple authors, and one author can write many books. In the logical model, we'll connect the book entity with the author entity using a many-to-many relationship.
As you can see the many-to-many relationship was converted into the two zero-to-many (0-*) references and one table, whose name (author_book) was created from the names of the two tables that had the relationship.
Associations → Tables
Each association is changed into one table. The table contains additional columns for related entities. The pattern is {foreign table name}_{foreign table's primary identifier name}. In the Northwind example, I've marked both sides of the association as Dependent. That means that a row in order_items cannot exist if it's missing any of the sides. On the ER diagram, these columns will be part of the primary key of the table that it's dependent on. In my case, both orders_order_id and products_product_id are part of the primary key for order_items.
Okay, this one will be a handful. For now, let me just briefly introduce each of the three scenarios. We’ll have a comprehensive explanation posted to this blog soon, so subscribe to be sure you don’t miss it.
As you know, tables in databases aren't like classes in Java – they don't inherit anything from anything. But from time to time, someone may have the idea to design a project in a way that the tables inherit from one another.
In such a case, the Vertabelo Modeler will generate the tables depending on the strategy selected for each parent-entity. To pick a strategy, click on the most generic entity (one that doesn't inherit from anything) and scroll down in the Properties Panel to the Inheritance section.
There you'll have the options with different generation strategies. Let's break down each of the strategies.
Inheritance Strategy #1 – One Table per Hierarchy
If you choose this, the Vertabelo Modeler will create one table for all children with all attributes from all entities. Additionally, it will add a column for a discriminator (that will tell us which class a given row represents). Note that you may also set the type of the discriminator.
All relationships connected to any of the children will be generated as references to the main table.
The logical diagram from the example above will be converted like so:
Inheritance Strategy #2 – One Table per Entity
For each entity, a table will be generated. The table will contain an additional column that refers to the parent by its primary key. In this strategy, all relationships of an entity will be changed to references to the corresponding table.
The logical diagram from the example above will be converted like so:
Inheritance Strategy #3 – Table per Entity (All Attributes)
This is similar to the previous strategy, but with one difference: the child table will have, not only columns from the respective entity, but also all the columns from the parent tables. The same goes for the relationships – all relationships from the parents will be added as references in each of the children.
The logical diagram from the example above will be converted like so:
Sequences
If your chosen database engine supports sequences and you left the Generate sequences checkbox checked, Vertabelo will add sequences to the physical model. You won't see them on the ERD, but you'll be able to see them in the Model Structure panel under the Sequences section. There also in the generated SQL file.
A sequence will be generated for all entities that have a single-attribute primary identifier of the Integer or LongInteger types. The name of the sequence will be generated according to this pattern: {entity name}_seq.
Generating Physical Diagrams Is Really That Easy
In Vertabelo, you can create models on any level of abstraction: conceptual, logical, or physical. You can start by designing the physical data model if you already know what you need. Or you can start with the logical model and reach your goals as you go, using Vertabelo to generate the physical model when you’re done.
