How to Implement a Conceptual, Logical, and Physical Data Model in Vertabelo

This article will lead you through the differences between the conceptual, logical, and physical data models. It will also show you how to create each one. 

What are conceptual, logical, and physical data models? What do they do, and what are the differences between them? That’s what I’ll answer in this article. It won’t be only theory; I’ll also show you how to create different data models using Vertabelo.

A data model describes data, data elements, and the relationship between them. When you build a database, a data model serves as the blueprint that allows the data architect to translate all the business processes to the database.

Conceptual vs. Logical vs. Physical Data Models

In data modeling, there are traditionally three levels or stages of database model development:

• Conceptual data model
• Logical data model
• Physical data model

If you’re interested in knowing more details about data modeling, ER diagrams, entities, and attributes, this in-depth article is just for you.

Now let’s see what each of these levels/stages are all about:

Conceptual Data Model

The conceptual data model is rather abstract and is concerned with high-level design. It’s often used to define, describe, and organize the most general business rules and processes. Since it’s usually developed for a business audience, it will show only entities and their relationships.

Logical Data Model

This data model is more complicated and detailed. Besides showing the entities and the relationships between them, the logical data model defines all the attributes and their details, such as optionality, data types, data precision, and data length.

The line between conceptual and logical data models is somewhat blurry. Both models are developed regardless of which Database Management System (DBMS) will be used. Because of that – and the fact that the conceptual data model is basically a logical data model with fewer details – in practice, there is rarely a distinction between these data models.

Physical Data Model

The physical data model’s main characteristic is that it’s always developed for a specific DBMS. This is the final phase of data modeling; all the primary keys, foreign keys, indices, user roles, authorizations, stored procedures, etc. should be defined.

Data Models in Vertabelo

Before I get into building the examples, you should know how modeling works in Vertabelo.

When modeling data, you have two diagrams available, the logical diagram and the physical diagram. The conceptual diagram is used for building the logical and physical data models. As I already said, the difference between the conceptual and logical model is not well defined. It’s almost as if the conceptual model is a half-finished version of the logical model. Since there’s no significant difference between those two models except the number of details shown on the diagram, they can both be modeled using a logical diagram. Depending on the details you’ve added to your model using the logical diagram, you make the model more “conceptual” or more “logical”.

The physical diagram’s purpose is, of course, to create the physical data model from the logical data model.

Conceptual Data Model Example

Let’s keep things simple. Your task is to build a data model for a company that employs freelancers from all over the world. The data model needs to store all the necessary employee details, including their job title, the department they work in, and the country they work from.

Here’s how the conceptual data model could look:

Let me explain what I did here. There are four entities in this model:

• employee
• job_title
• country
• department

The employee is the central entity to which all other entities are connected by a one-to-many (1:N) relationship.

The relationship between the employee and department entity is that the employee works in a department. It’s a 1:N relationship because one employee can only work in one department, but one department can have none, one, or many employees.

The employee also holds a particular job title. That’s why there’s a 1:N relationship between the employee and job_title entities; one employee can hold only one job title, but one job title can be held by none, one, or many employees.

Finally, the employee also lives in a specific country. The relationship between the employee and country entities is also 1:N; one employee can live only in one country at a time, but none, one, or many employees can live in the same country at the same time.

In this model, I’ve only defined entities and their relationships, which aligns with the definition of a conceptual data model.

Logical Data Model Example

Now that there’s a conceptual data model, I can add more details to it and make it the logical data model.

Here’s the same model as above, but with more information:

Those four familiar entities now contain more details; let’s see what they are.

The job_title entity contains the following attributes:

• id – The ID of the job title; this serves as the primary identifier (PI).
• name – The name of the job title.
• description – The job title description.

The attributes in the country entity are:

• id – The ID of the country and the primary identifier (PI).
• name – The name of the country.

The third entity is the department, with the following attributes:

• id – The ID of the department and the primary identifier (PI).
• name – The name of the department.

The last entity is the employee, containing the attributes:

• id –The ID of the employee and the primary identifier (PI).
• first_name – The first name of the employee.
• last_name – The last name of the employee.
• start_date – When the employee started working.
• end_date – When the employee stopped working.

As you’ve probably noticed, all the entities now have attributes. I’ve also specified data types. You’ll see a letter M near most attributes; this means this attribute is mandatory. Where there’s no M, the attribute is optional.

If you’re struggling with creating your logical diagram, this step-by-step guide will guide you through it.

Physical Data Model Example

Now that you’ve created the logical data model, making a physical one is really simple in Vertabelo. Go to your logical data model and right-click on it to see what you can do with it. The option you’re looking for is  at the bottom: “Generate physical model…”.

The next step is to choose a database engine and other options.

Clicking on the yellow button will create a physical data model, which is shown below. The only changes I’ve made manually are to the order of the columns in employee; I’ve also adapted relationships. Hence, they go directly from the primary key (PK) to a foreign key (FK). These changes are only cosmetic and do not impact the model’s functionality; everything else was done automatically by Vertabelo.

As you can see, the previous four entities have now become four tables. All the attributes stayed the same; all the data types stayed the same. The primary identifier (PI) has now become the primary key (PK). Only attributes having a letter N besides them can have a NULL value. You can see that these were the optional attributes in the logical data model.

The main change is in the table employee. Besides the attributes specified in the logical data model, there are three new columns:

• job_title_id – The ID of the job title; references the table job_title.
• department_id – The ID of the department; references the table department.
• country_id – The ID of the country; references the table country.

These three new columns are foreign keys (FK), whose role is to connect the table employee with the other three tables.

By creating all three data models, you’ve come to the end of today’s article.

Getting Familiar with Data Modeling?

By reading this article, you learned the fundamental distinctions between conceptual, logical, and physical data models. Using a simple database example, we’ve put this theory into practice. If you followed everything we discussed, you created your first three data models using Vertabelo.

If those examples are not enough, you can always build other models and try to solve some data problems you’re familiar with. Feel free to use the comments section to let me know how this article helped you understand all three data models.