Tables

Now, we're going to see how tables look in Vertabelo and try to create an example of a table representing a person. For this example, what we want to know about people is:

first name
last name
date of birth
number of children
height
possesion of driver's license
number of driver's license (if possessed)

Let's put this piece of reality into a database model.

Adding a new table

To add a new table, click the Add new table button on the toolbox or press 3 on your keyboard.

Or, you can use Model Structure.

Then, click wherever you want to add a table to your model.

Table properties

In Primary data we can rename our table and provide a description of the kind of data the table is going to store. Now, we're going to dive into a little bit more advanced table properties.

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 and also an ID column to be able to differentiate between people.

Obviously, we have to change types of columns since people's names aren't going to be numbers. We can achieve that in two ways. Firstly, there is a type menu under the button, where we can choose the type from the most commonly used ones and set its length or precision if needed.

Secondly, we can set the type using the keyboard in the Columns section. Types not listed in the type menu, like JSON, may still be used by just typing its correct name.

If we try to use any type which is not supported, Vertabelo will show us a warning.

To set the column to be a primary key or to be nullable, you just have to check the N or PK box respectively. The id column is, of course, chosen to be a primary key and the drivers_license_num column is set as nullable, in case a person does not have a driver's license.

There are some more advanced options under the button. 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 too.

To ensure that the data is always sorted correctly we can use collations.

Copying columns between tables

To copy some columns from one table to another, open the Columns section in the right panel for the first table, and click on the dragging button to select a column. You may select a few of the columns by using Ctrl (Cmd) and or Shift keys.

Columns

When you've selected all the columns you wanted simply copy it by pressing Ctrl+C (or Cmd+C).

Selected columns

Click on the table you want to add the copied columns, and paste them by pressing Ctrl+V (or Cmd+V). As you can see, the copied columns were pasted:

Pasted columns

Alternate keys

The next section in Table Properties is Alternate keys. Here we can create an alternate key and add columns to it. For the Person table we want to have an alternate key based on people's first name, last name and date of birth. Of course, we must assume that such a combination is unique in our database.

Indexes

Let's say we want to have access to the 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, of course, 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 height and children_number are reasonable.

Additional SQL scripts

For more advanced users, who can't achieve their goals using standard Vertabelo UI controls, there is an Additional SQL scripts section, where scripts to execute before and after creating a table can be added.

Additional properties

In Additional properties you can find options usually specific to a chosen database engine. Here are the properties for PostgreSQL.

Format

The Format section enables us to change the fill and line colors and set the table's size to be fixed.

This is how our table looks after formatting.

SQL Preview

At every stage of creating a table you can check the SQL Preview of its current state.

Here's the SQL Preview of the Person table.