Back to articles list
There is more to a database structure than just tables. Tables are logically divided into groups and stored in database schemas. Read along to find out how to include schemas in your ER diagram using Vertabelo.
A database consists of one or more database schemas, and a database schema consists of one or more tables. A good data modeling tool helps you set them all up. In this article, you’ll see how to create database schemas in Vertabelo and assign tables to them.
Need to recall some basics on data modeling? Make sure to check out the article “Data Modeling Basics in 10 Minutes” before jumping into database schemas.
Let’s get started.
How to Create a Schema in Vertabelo
First things first! Log in to your Vertabelo account and create a model.
Let’s create a physical data model by clicking the Create new document icon in the menu toolbar.
Next, we choose the physical data model.
Now, we give a name to our model and choose a database engine.
Before we start creating tables, let’s create the schemas to be used in this model.
On the right-side panel, you see the Model Properties pane. To create schemas, expand the Additional SQL scripts tab, like this:
Now, let’s input the SQL statements for schema creation.
The CREATE SCHEMA statement is very straightforward. It creates a schema with the name specified in the statement. Note that there is no semicolon after the last statement. Vertabelo adds it by default as you’ll see in the upcoming examples.
To learn more about the Additional SQL scripts tab, read the article “What Are Additional SQL Scripts and What Do You Use Them For in Vertabelo?”
Now, our database schemas are ready! The next step is to create tables and assign them to the schemas.
In this article, you find some good tips on how to work with Vertabelo editor features efficiently.
How to Set a Schema in Vertabelo for Tables
Below is the ER diagram of the Company data model.
Starting from the left, the EmployeeDetails table stores the contact details of each employee. That’s why there is a one-to-one link between the Employee and the EmployeeDetails tables. The Employee table stores the department, the position, and the salary amount for each employee. These two tables are in the employees schema.
Next, the Client table stores a unique identification number for each client and a unique identification number of the employee assigned to the client. Each client is assigned to one employee, and each employee is assigned to zero or more clients. Similar to the EmployeeDetails table, the ClientDetails table stores the contact details of each client. These two tables are in the clients schema.
Real-world ER diagrams consist of many tables. Often, it is difficult to locate specific tables in an ER diagram. As an ERD modeler, Vertabelo offers different ways of finding them. Check them all out here!
Now, let’s assign the Employee and EmployeeDetails tables to the employees schema.
To do so, we first select the Employee table and go to the Table Properties pane on the right-side panel. In the Additional properties tab, we set our schema as below.
Let’s follow the same process for the EmployeeDetails table.
To verify that both the Employee and EmployeeDetails tables are in the employees schema, we generate SQL scripts by selecting each table one by one and clicking the SQL preview button on the right-side panel.
The SQL scripts generated for each of the tables start by creating the employees and clients schemas (here is the missing semicolon!).
We know a table is created in a particular schema when a schema name precedes a table name with a dot in between. The syntax is <schema_name>.<table_name>.
In the generated SQL scripts, we see two CREATE TABLE statements that create the Employee and EmployeeDetails tables in the employees schema. Notice the table names used in the CREATE TABLE statements are employees.Employee and employees.EmployeeDetails. It means our tables belong to the employees schema.
Now, let’s assign the Client and ClientDetails tables to the clients schema. The process is the same as shown above for the Employee table.
And we get the following SQL scripts:
Just like with the employees schema, the Client and the ClientDetails tables are created in the clients schema.
As a data modeling tool, Vertabelo generates scripts not only for creation but also for many others. Make sure to check out this article to learn more.
How to Set a Default Schema for New Tables in Vertabelo
You may want to have a default schema to which all the newly created tables are assigned. Let’s set it up!
We are going to add more tables to the employees schema. But we don’t want to waste time assigning a schema to each table individually. No problem! We set the employees schema as the default schema for each table created in Vertabelo from now on.
To do so, we go to the Model Properties pane, expand the Default Additional Properties tab, and set the schema value to the employees schema, like this:
Let’s create a table to check if it works.
We’ve created the Department table assigned to the employees schema by default.
The Department table stores the unique identifier, the name, and the location of each department. For the sake of completeness, the Department table is linked to the Employee table. Each employee is assigned to exactly one department, and each department has one or more employees.
Here is the SQL script for the Department table:
As you see, CREATE TABLE creates the Department table in the employees schema, as expected.
Divide and Conquer With Database Schemas
You may wonder why it is beneficial to use multiple schemas in a database. Database schemas let us divide the database and conquer the user access rights to each schema for each type of user.
Let me explain with our Company data model example we have used throughout this article. Imagine a requirement that an employee be able to search clients in the database without being able to access other employees’ data. This is implemented using schemas. In this case, employees have access rights to the clients schema but not to the employees schema.
Now You Know How to Set Schemas in Vertabelo!
Now, you’re all set. Vertabelo is a great data modeling tool that lets you do all this. Go ahead and create your own database with multiple schemas. Good luck!
