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.
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
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
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
EmployeeDetails tables to the
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
To verify that both the
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
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
In the generated SQL scripts, we see two
CREATE TABLE statements that create the
EmployeeDetails tables in the
employees schema. Notice the table names used in the CREATE TABLE statements are
employees.EmployeeDetails. It means our tables belong to the
Now, let’s assign the
ClientDetails tables to the
clients schema. The process is the same as shown above for the
And we get the following SQL scripts:
Just like with the
employees schema, the
Client and the
ClientDetails tables are created in the
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.
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
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
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!