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
- possesion of driver’s license
- number of driver’s license (if possessed)
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.
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.
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.
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.
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.
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.
In Additional properties you can find options usually specific to a chosen database engine. Here are the properties for PostgreSQL.
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.
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.