Back to articles list
A database schema is used to define the structure of the tables and the relationships among them, the data types, and the constraints in a relational database. Sometimes, the word schema is synonymous with the entire database. In this article, we go through the concepts that define a database schema and how these concepts are represented in Vertabelo.
A database schema is the logical ordering and organizing of the elements and the data in a database. There are different levels of database schemas, each responsible for representing a certain level of information about the structure, the objects, and the type of data stored in the database.
There are three types of database schema: the conceptual database schema, the logical database schema, and the physical database schema. While each represents the same information, they do so at different levels of detail because their purposes are different. The physical schema is the final schema created, containing all of the database objects including the tables.
What Is Included in a Database Schema?
To reiterate, a database schema is just a way of organizing the data inside a database. Data is stored in database objects like tables, and other objects related to the tables like indexes and constraints are also part of the database schema. Additionally, there may be objects containing business logic, such as stored procedures or functions, that are part of a database schema.
Sometimes, a database contains multiple schemas to logically separate database tables and related objects. This also means each database object can be stored in the schema to which it is more logically related.
Types of Database Schema
As mentioned before, there are three types of database schema: conceptual schema, logical schema, and physical schema. Of these, the last two are the most important.
A conceptual database schema is simply a high-level view with a description of the objects and their relationships selected to solve your problem.
A logical database schema is a more detailed version of the conceptual schema. It contains the definitions of all database objects like entities and attributes as well as of the constraints. Although it appears complete, it is more of a rough model of the final, physical schema that is actually implemented in a database.
The physical schema represents the actual design of the database at the physical level. Everything you describe in this schema needs to be appropriate for the database you’re going to build. This includes the tables and their internal structures, whether they are partitioned or not, the column data types, what indexes need to be created, where the data is stored in terms of database files, and other details.
There are important things to consider when creating a physical schema of your database. We recommend you take a look at our list of things to consider when creating a physical schema.
Creating a Database Schema
To create tables and store data in the database, we first need to create our schema to place all objects related to each logical part of the problem. If you do not do so, the objects are assigned to the default schema of the database. For example, the default schema in PostgreSQL is called public, and the default schema in Microsoft SQL Server is called dbo.
The syntax for defining and creating a database schema may differ slightly depending on the database programming language. The following is a T-SQL command, specific to Microsoft SQL Server, for creating a database schema:
USE <database>; CREATE SCHEMA Sales;
Once a schema is created, we need to grant access if another user wants to access the objects inside it. There are different levels of access control. We may limit a user to read-only access to the objects inside a schema. Alternatively, we may also grant certain users write access to insert data into the tables inside a particular schema.
Granting Read Access to a Database Schema
In T-SQL specific to Microsoft SQL Server, use a command similar to the one below to GRANT schema read permission:
GRANT SELECT ON SCHEMA :: SALES TO guest;
Once you have access to the schema, you can select data from the objects in it. The best practice is to qualify object names with their respective schemas. This becomes necessary if your database has multiple schemas. For example, if you create the Sales schema as we have above, then writing a SELECT query to get data from the tables inside this schema looks like:
SELECT * FROM SALES.ITEM_SALES;
Otherwise, we get an error saying our object does not exist in the default schema. Different schemas have different objects inside them; it is important to qualify object names with their schema names.
Once we create the Sales schema, we can create tables that, for example, contain information specific to sales. The type of data that goes in the Sales schema may be similar to what is shown in the diagram below.
In a database, we don’t specify an area around the tables to put them into a schema. Rather, we need to assign them their respective schemas.
Setting up a Database Schema in Vertabelo
To define and create a database schema in Vertabelo, open the Vertabelo Modeler and go to the "Model Properties" panel on the right. In that section, expand the "Additional SQL Scripts" section and add the statement that creates the schema. In our database schema example, it looks something like this:
In Vertabelo’s database modeling tool, this is done easily by selecting the table for which you want to set a schema and going to "Table Properties." Look for the "Additional Properties" field and fill in the name of the database schema for the table, just like in the screenshot below.
We can double-check that the schema has been applied to our table by checking the SQL DDL statement for the Sales table. Select the table, then press the blue "SQL preview" button at the top right. This generates a pop-up with a preview of the SQL statement to create the table. There, we see that the table name is prefixed with the schema name Sales. Other database schema SQL scripts we have already configured, such as creating the schema itself, are there as well.
If you need more help navigating through the exact steps to take, we have a few short articles on how to create a table in Vertabelo.
Adding Tables to the Default Database Schema
Sometimes, we want most or even all of our tables to be created in the default schema. In that case, we don’t want to go to each table in our physical model and set the schema one by one. Rather, we want to have each table created automatically in the default schema.
To set the default table schema in Vertabelo Modeler, first, make sure you do not have any object selected. Then, expand the "Model Properties" menu in the top right part of the screen. Once expanded, scroll down until you see the section "Default Additional Properties" and expand that item. You now see a section for "Table" with an input field for "Schema."
In the screenshot above, the value of "GeneralSchema" is added. This means that the schema is automatically set to "GeneralSchema" every time we add a new table in Vertabelo Modeler.
However, be aware that this is valid only for newly created tables. You need to manually change this if you want the tables you've already created to belong to the default schema. To do so, go into each table and change the schema to "GeneralSchema" or the name of your default schema.
Take a look at this article for more details on how to set database schemas, as well as explanations around the context of setting up schemas and how they relate to the overall database design.
Database Schemas: Just a Part of the Database Design Journey
Defining and configuring the database schema, building the physical schema, and defining the objects in it are just a few steps on the road to learning all of the intricacies of database design. If you enjoy building databases and like to delve into the smallest details, take a look through our list of articles on database modeling and design fundamentals.
