Back to articles list
- 12 minutes read

How to Create Physical Diagrams in Vertabelo

So you don't like writing all of your SQL CREATEs by hand? Design your database with Vertabelo and let it generate the SQL file for you!

As you may already know, there are three different levels of data models: conceptual, logical, and physical data models. The conceptual model is the most abstract, while the logical model has a few more technical details. The physical data model defines all the details needed for a specific database: column data types, primary and foreign keys, constraints, indexes, sequences, views, and other physical objects.

Today, we'll focus on building a physical diagram in Vertabelo and using that to generate the SQL CREATE statements for a new database. If you'd like to know more about other data models, check this out. You can also read our article on how to model conceptual and logical diagrams in Vertabelo. Some may say that physical diagrams are the most important; they'll definitely help you model your database or see how an existing database looks.

Getting Started

To start, go to my.vertabelo.com and log in. Then, click on the Create new document button.

Physical Diagrams in Vertabelo

Next, click on the Create button next to Physical data model.

Physical Diagrams in Vertabelo

A pop-up menu will ask you for the name of the document, the database engine you'd like to use, and whether you'd like to start with an empty model, an example, an SQL file with CREATE statements, or an XML file (which you can get from exporting your database or by using our reverse engineering tool).

Physical Diagrams in Vertabelo

Tip: To tell the logical and the physical data models apart, look at the icons:

Physical Diagrams in Vertabelo

The physical data model icon has a tiny diagram and a small database barrel, emphasizing that it deals with the physical database. The logical data model icon has just a tiny entity diagram. The same icons are used in the model list.

Adding Objects to the Physical Data Model in Vertabelo

Physical Diagrams in Vertabelo
Select an element (press 1)
Select an area (press 2)
Add a new table (press 3)
Add a new reference (press 4)
Add a new view (press 5)
Add a new note (press 6)
Add a new subject area (press 7)

You may also add new elements by right-clicking or clicking on the menu in the left panel.

You don't have to memorize all of this; each button will show you a tooltip with the description and its shortcut. If you want this information close at hand, bookmark this page or check out the Vertabelo documentation.

Physical Diagrams in Vertabelo

Tables

After selecting the Table tool from the toolbox, click on the diagram where you want to add a new table. In the right panel, you'll be able to rename the table, add columns to it, and more. We'll name this new table Person.

Physical Diagrams in Vertabelo

Columns

Use the Columns section to decide how the person will be represented in our model. Let's add an appropriate column for every piece of required information (use the + Add column button) and an ID column to be able to differentiate between people.

Physical Diagrams in Vertabelo

Obviously, we have to change column types, since people's names aren't going to be numbers.There is a type menu under the gear icon ( ), where we can choose a type from the most commonly used ones and set its length or precision (if needed).

Physical Diagrams in Vertabelo

Note: Data types will vary depending on the database engine you use. You can change the type of the column just by typing in the field. If you'd like to use a custom data type like JSON, just type it in. A warning might be displayed if Vertabelo doesn't recognize the data type. (Vertabelo knows about rare data types that aren't listed in the Data Type menu.)

To set the column to be a primary key or to be nullable, you just have to check the PK box or the N box, respectively.

There are some more advanced options under the down-arrow icon (). In our example, when the number of children is not provided, we want to have it set to 0. Using a check expression, we won't allow the children_number to be negative.

Physical Diagrams in Vertabelo

Indexes

Let's say we want to have access to records for people born on the same day. To make such queries run faster, we can use indexes. All we have to do is add a new index and then add the date_of_birth column to it. Sorting in descending order will store the dates from newest to oldest. Such an index can't be unique, so we should set it that way.

Physical Diagrams in Vertabelo

Checks

Besides creating a check expression for each column, we can also create a check expression for the whole table. That way, we can ensure that the values of the height and children_number columns are reasonable.

Physical Diagrams in Vertabelo

Additional SQL scripts

For more advanced users who can't achieve their goals using the standard Vertabelo UI controls, we have the Additional SQL scripts section. You can use it to add scripts to execute before and after creating a table.

Additional properties

In Additional properties, you can find options that are usually specific to a chosen database engine. For example, here are the properties for PostgreSQL.

Physical Diagrams in Vertabelo

Format

If you'd like to change the colors of a table (or any other element) go to the Format section in the right panel. You can pick a color from the predefined grid or type in a hex value.

Physical Diagrams in Vertabelo

References

To create a reference, select the Reference tool from the toolbox and drag your cursor from the first table to the second (referenced) table. When you release the mouse button, the reference will be created and a new foreign key column is automatically added to the first table.

If you don’t want foreign keys to be generated automatically, you can switch this feature off in your account preferences. If you don’t want Vertabelo to generate column names, you can change them in Table Properties without any problem.

Physical Diagrams in Vertabelo

In the right panel, you'll be able to select the reference type (and its derivatives) from the list:

If you feel confused about the reference symbols, see this concise explanation.

Views

To create a view, select the Views tool from the toolbox and then just click wherever you want the view to be applied. A view lets you create a virtual table containing only the data you want, e.g. the data which you often use after joining a couple of tables.

Let's use the example of an ER diagram: we may, for example, often check which categories are the most important for a client.

Physical Diagrams in Vertabelo

Vertabelo guesses the names for the columns based on the defined query. As you can see, the name for the COUNT() column wasn't guessed properly, so we need to update it manually.

As for the tables, you can add additional SQL scripts and set database-specific properties, too.

Additional SQL Scripts

You may add custom SQL scripts that will be included in the downloadable SQL file that the program will generate. To edit those scripts, first make sure nothing is selected; then, in the right panel, type in any SQL statements you want to have executed.

Physical Diagrams in Vertabelo

Adding ER Diagram Features in Vertabelo

Subject areas and text notes help you keep your data model organized.

Text notes

To add a text note, just select the Text Note tool from the toolbox or press 6 on your keyboard. Then click wherever you want to put a note on the diagram; in the properties panel, you can change the note’s content and its color:

Physical Diagrams in Vertabelo

Subject areas

Databases can quickly get very large. They’re often full of tables, references, sequences, and views – and all these things together make it really easy for a database architect to get lost. Luckily, when you design your database using Vertabelo, you can use subject areas to avoid feeling overwhelmed by model elements. With subject areas, we can:

  • Increase the model’s readability.
  • Group model elements according to their functions.
  • Make it easy to navigate through the model.
  • Simplify searching for elements.
  • Use the same table several times in one model.

To add a new subject area, either click on the Subject Area tool in your toolbox or press 7 on your keyboard. Click and hold the left mouse button and drag your cursor to draw a box around all of the elements that you want in that area.

Physical Diagrams in Vertabelo

In the properties, you can change the area’s name and give it a unique look by changing the colors or line style. We'll modify the Vertabelo example diagram, naming one area Purchase and creating another area called Product.

Physical Diagrams in Vertabelo

If you take a look at the left panel (Model structure), you'll see that under Subject areas you can quickly access any of the elements:

Physical Diagrams in Vertabelo

You can also make the subject areas overlap:

Physical Diagrams in Vertabelo

The purchase table will be visible in both subject areas in the Model structure. There is another way to have a table visible in both subject areas – shortcuts.

Shortcuts

Shortcut tables are especially helpful when your database has a lot of tables or references. They will allow you to place a representation of your table on the diagram as needed, which can make the diagram more legible. Don't worry – you won't be duplicating your table; if you click on one representation and change something (e.g. add a column), this change will be visible in all the other shortcuts. ??

To create a shortcut table, you may either:

Right-click on the table in the Model structure and select Add shortcut table.

Physical Diagrams in Vertabelo
  • Simply drag the table from the Model structure onto the diagram.
Physical Diagrams in Vertabelo

Shortcuts are automatically numbered ...

Physical Diagrams in Vertabelo

... but if we drag them into the subject areas, they will inherit the second part of their name from the subject area.

Physical Diagrams in Vertabelo

You may notice that references between the tables are missing. You can restore the references where you need them by selecting the tables (holding down the Control or Command key), and clicking this option from the toolbar:

Physical Diagrams in Vertabelo

Remember that if a given reference's line is dashed it means that it's just one of its representations on the diagram; if you delete this reference, it doesn't delete the reference between the tables. If the line is solid it means you're dealing with the only copy of that reference: deleting it will remove the relation between the tables. Don't worry if you remove a reference that way – you can restore it by undoing (Ctrl+Z / Cmd+Z / clicking on the button in the main toolbar). Or you can take a sneak peek at the past with Version Control.

So we've restored two references: one between the client and purchase tables in the Client subject area, and the second between the purchase and purchase_item tables in the Purchase subject area.

Physical Diagrams in Vertabelo

There are more details about subject areas in the Vertabelo documentation.

Generating SQL Scripts from the Physical Data Model

To generate an SQL script, click on the SQL file button in the main toolbar (), choose whether you want to generate a Create or Drop script, and identify which elements should be included in it. Then click Generate.

Physical Diagrams in Vertabelo

You can download the file once it’s finished.

Physical Diagrams in Vertabelo

Exporting the ER Diagram to PDF, XML, or as an Image

You can also export your diagram to PDF, XML, or as an image – simply click on the proper icon from the main toolbar.

Physical Diagrams in Vertabelo

The mechanism for generating PDFs and images is the same: after clicking the button in the menu or toolbar, a pop-up will appear where you can adjust the output file. Once you’ve got everything as you want, click Generate; when the file is done, click Download.

Note: Clicking on the Export to XML option will download the XML file immediately.

Model Validation / Handling Problems

Vertabelo verifies your diagram constantly while you're working on it – if something is wrong, you'll see it in the right panel, in the Problems area. If there are no problems with your model, the Problems area will be gray.

Physical Diagrams in Vertabelo

If you expand the Problems area when it’s gray, you’ll see that there are neither errors or warnings in your model.

Physical Diagrams in Vertabelo

But, if something goes wrong, Problems will turn orange and the elements with problems will be marked with an error or a warning icon.

Physical Diagrams in Vertabelo

In the Problems section, you can check the details about what caused the errors/warnings and where to find it. The difference between this Problems section and the Problems section in the Properties panel is that here you can see all of the problems flagged by Vertabelo in your database model. Clicking on a reported problem will take you to the place in the model where it occurred, selecting the element with the problem and focusing your cursor on the property that caused it.

Physical Diagrams in Vertabelo

Sharing / Collaborating

If you'd like to start collaborating on a model, you may either click the Share button () in the main toolbar while you're in the model or right-click the model while you're in Drive view.

Physical Diagrams in Vertabelo

A pop-up menu will appear:

Physical Diagrams in Vertabelo

You may change the type of access for a given user by clicking the pencil icon and selecting the right option. You may also revoke access by clicking on the X button.

To grant access for new users, simply start typing their email address (or name) into the field, pick the correct role (from the list on the right), and decide whether they need a notification about this (the Notify people checkbox) and if this notification should contain a personal message (click on Add personal message and type it in).

When sharing a model in Vertabelo, you can decide each user’s privileges:

  • Is owner – Grants full access (control over sharing, editing, etc.).
  • Can edit – Allows the user to edit the model but not to change sharing options or publish the model on the web.
  • Can view – The user can do nothing but view the model.

Collaboration in Vertabelo is a very broad topic; see this article for more details.

More Help with Physical Diagrams in Vertabelo

Vertabelo is easy to work with and will help you create your database structure. There is so much information on this topic that you might want to bookmark this article and come back later for a refresher – or a quick sanity check. Vertabelo’s documentation is also a good place to search for further enlightenment. If you have a question and can't find the answer for it, check out our FAQs  or contact our support team.

go to top