Back to articles list
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.
Next, click on the Create button next to Physical data model.
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).
Tip: To tell the logical and the physical data models apart, look at the icons:
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
 | 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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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:
You can also make the subject areas overlap:
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.
- Simply drag the table from the Model structure onto the diagram.
Shortcuts are automatically numbered ...
... but if we drag them into the subject areas, they will inherit the second part of their name from the subject area.
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:
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.
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.
You can download the file once it’s finished.
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.
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.
If you expand the Problems area when it’s gray, you’ll see that there are neither errors or warnings in your model.
But, if something goes wrong, Problems will turn orange and the elements with problems will be marked with an error or a warning icon.
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.
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.
A pop-up menu will appear:
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.
