Back to articles list
Logical models define the initial blueprint of your database. Read along to learn how to create them and what attribute data types are available in Vertabelo.
In this article, we’ll introduce the logical model. It essentially illustrates the structure of entities, attributes, and relationships in your database. Also, you’ll see the difference between logical and physical models. In the end, we’ll create a logical model in Vertabelo, which will include creating entities with attributes of specific data types.
Let’s get started.
What is a Logical Model?
A logical model visualizes database tables, columns, and the connections between them. In a logical model, the tables are called entities and the columns are called attributes.
Let’s look at the logical model below.
We’ve got three entities here: Order, Product, and ProductCategory. Each of these entities has its own attributes. For example, the attributes of the Order entity are Order_Id, Product_Id, Order_Date, and Ordered_Amount.
The attributes can be divided into two groups: key and non-key; for instance, the key attribute of the Order entity is Order_Id. Also, each attribute is assigned a data type, as we’ll discuss later.
The above logical model also contains information about the relationships between the entities. There is a one-to-many relationship between the ProductCategory and Product entities. So one category can be assigned to many products, but one product can have only one category assigned to it. Also, there is a many-to-many relationship between the Product and Order entities. As you’ve probably already figured out, one product can be assigned to many orders and one order can have many products assigned to it.
So the logical model is a diagram presenting entities (tables) with their attributes (columns) and the relationships between them. Thus, a question arises: How is a logical model different from a physical model?
Logical Model vs. Physical Model
Both logical and physical models are diagrams that help us visualize database structure. The main difference between them is that a logical model is not database-specific. On the other hand, a physical model is designed for a specific database engine (e.g. MySQL, Oracle, PostgreSQL, etc.).
A logical model provides business information and rules about the database. Here, the entity and attribute names don’t need to resemble table and column names. The physical model must have sufficient information to build the database, including table names, column names, database-specific data types, and other database objects. The Vertabelo data modeler can even generate SQL scripts that create a database from your physical model.
As shown in the above image, Vertabelo lets you convert a logical model into a physical model and then generate an SQL database creation script from the physical model.
When designing a database, you’d first use a logical model to define the database structure. After deciding on your database engine, you’d convert your logical model to a physical one. If you want to know how to convert a logical model to a physical one, check out our article on How to Generate a Physical Diagram from a Logical Diagram in Vertabelo.
And if you need more abstraction, you could go for a conceptual model instead of a logical model. To learn more about conceptual, logical, and physical models, follow along with this article.
Compare the physical model below with the logical model we discussed in the last section.
This physical model looks different from the logical model. The data types assigned to attributes (now columns) are now database-specific. For example, Integer from our logical model became int in our physical model, Varchar(50) became varchar(50), and Date became date.
There is also another table in our physical model. This is because of the many-to-many relationship between the Product and Order entities in the logical model. Many-to-many relationships are commonly implemented in a database using a junction table. So now we have two one-to-many relationships (one is between Product and OrderProduct and another between Order and OrderProduct) instead of one many-to-many relationship.
You can learn more about logical diagrams here. For now, we’re ready to create a logical model in Vertabelo.
Let’s Create a Logical Model
First, we create the logical model in Vertabelo, as shown below:
Step 1: Create a new document:
Step 2: Create a logical data model:
Step 3: Name your logical model and start modeling:
Next, we’ll create entities with attributes. We’ll also model the relationships between entities.
On Entities
There are two ways you can create an entity in Vertabelo.
One way is to choose (4) Add new entity from the toolbar and then click on the working area to create an entity, as shown below:
Another option is to create an entity from the Model Structure sidebar:
After creating an entity, we give it a name and assign it at least one attribute (hint: it should be a key attribute):
Now, we can add more key or non-key attributes to our Product entity.
On Attributes and Their Data Types
If you want to add attributes to an entity, click on the Add attribute button, as shown below:
Give each attribute a meaningful name:
Next, select a data type for this attribute. You can choose from the data types available in Vertabelo by clicking the settings icon (in the red box) next to the Data type text field:
Let’s look at the data types available in Vertabelo.
Vertabelo lets you choose from standard data type groups, such as numeric, string, date and time, large objects, and others.
Attribute Data Types in Vertabelo
You can choose a data type from the ones provided by Vertabelo. It has its advantages, such as, during the conversion of a logical model into a physical model, these data types are converted to appropriate data types for a specific database engine.
Build Your Own Logical Model in Vertabelo!
Creating a logical model in Vertabelo is not a complicated task. Our data modeling program has an intuitive interface that lets you easily create and work with logical models. You can convert a logical model into a physical model; later, you can use the physical model to generate the SQL code needed to create a database. Try it out yourself at Vertabelo.
Good luck!
