What Are Conceptual, Logical, and Physical Data Models?

Depending on the purpose, we may need to create either a conceptual, logical, or physical data model. Find out the differences and use cases for each one.

Data modeling implies identifying and defining entities and their relationships for a business solution. It requires a good understanding of the desired business outcome and is the foundation for creating a robust software solution.

The different model types (conceptual, logical, and physical) have different levels of detail and are used at different stages of the software development process. In this article, we are going to review each one, understand their different features, and explain when to use them.

Conceptual, Logical and Physical Data Models Explained and Compared

Data models evolve from conceptual (i.e. a quick, high-level view of the business requirements) to logical (where the entities involved are expanded and include more detail) and finally the physical data model, which can be implemented with a specific database provider (like Oracle, SQL Server, or MySQL).

Conceptual Data Model

A conceptual data model usually just includes the main concepts (entities) required to store information and the relationships that exist between these entities. We don’t usually include any details about each piece of information. We can consider the conceptual stage as an initial model, without all the details required to create a database.

Normally, entities are specified at a high level, using business rather than technical names; this allows the models to be understood by management and users and not just the technical staff. The main use of a conceptual data model is to define the scope of a business solution without going into any details.

Conceptual data models are represented using a data structure diagram (DSD), a predecessor of the well-known entity-relationship diagrams (ERD). Below we can see a very simple conceptual data model that represents authors, books, and publishers.

Conceptual data models are usually very simple, but sometimes the data structure diagrams used to represent them also include basic definitions of entities’ attributes, as shown below:

Logical Data Model

A logical data model is probably the most-used data model. It goes beyond the conceptual model; it includes entities, relationships, details on entities’ different attributes, and unique ways to identify entities (primary keys) and establish the relationships between them (foreign keys).

A logical data model should contain all the details to define an information domain. However, it does not consider the technologies (i.e. the database and platform) that will be used to implement the model. It should use business names for entities and attributes, since its purpose is to describe the data structures required, not to create the actual database.

Most software designers (architects, solution designers, and software analysts) may skip a conceptual data model and start directly with a logical data model. The line that separates them is not defined, nor is the level of detail set for each one. To learn more about logical data models, I recommend reading What a Concept! Is Logical Data Modeling Obsolete?.

Physical Data Model

A physical data model is usually derived from a logical data model for a particular relational database management system (RDBMS), thus taking into account all technology-specific details. One big difference between logical and physical data models is that we now need to use table and column names rather than specifying entity and attribute names. This allows us to adapt to the limits and conventions of the desired database engine. We also provide the actual data types and constraints that allows us to store the desired information.

Physical data models should be easy to transform into a SQL script that allows us to create the database structure. Most design tools (like the Vertabelo Database Modeler) allow the generation of a database creation script from a physical data model. To learn more about data modelers, try the article How to Draw an ER Diagram Online.

Physical data models may also include additional details – e.g. indexes that facilitate faster data access.

Conceptual vs. Logical vs. Physical Data Models

The following chart describes the different features that are usually included on each of these three models:

Feature	Conceptual Data Model	Logical Data Model	Physical Data Model
Entity Names	✓	✓
Entity Relationships	✓	✓
Attributes		✓
Primary Keys		✓	✓
Foreign Keys		✓	✓
Table Names			✓
Column Names			✓
Column Data Types			✓

We can consider both conceptual and logical data models as technology-agnostic, business-oriented models (the logical one with more detail), while physical data models are created to actually implement and create a database, thus taking into account technology features (data types, name conventions and limits, etc.).

From a single conceptual data model, we would expect to get a more detailed logical data model, both of them designed to represent the required data structures from a business perspective. From that logical data model, we can then create as many physical data models as needed, each of them designed for a particular database engine, as shown below:

Using Vertabelo to Create Data Models

The Vertabelo Database Modeler has features that allow us to define our database structure and then implement it in a RDBMS relatively easily:

• Vertabelo Logical Diagrams: This tool allows us to create logical data models in a simple way. Check out Vertabelo Features: Logical Diagrams to learn more.
• Vertabelo Physical Diagrams: We can easily convert any logical diagram into a physical one with just a couple of clicks. Once we create the model, we can apply all the required changes to verify that the model complies with our desired RDBMS’ specifications and our naming conventions. Learn more at How to Generate a Physical Model from a Logical.
• SQL Script Creation Tool: This tool allows us to generate a database creation script from our physical diagram, making the process of creating a database model very simple and straightforward.

Although Vertabelo does not include a conceptual diagram per se (as we explained, many solutions don’t require it and most database architects prefer to start directly with the logical model), nothing prevents you from creating a logical diagram with no attributes and using it as a conceptual data model. Each user or company can include the desired level of detail based on their requirements.

Creating a Database, From Conceptual Model to Generation Script

Let’s take a few moments to review a very simple school system and their different conceptual, logical, and physical models.

Creating a Conceptual Data Model Using a Logical Diagram

A conceptual model, as explained above, will normally have only the entities and the relationships that exist between them. For our school system, we have defined the following four entities:

• Student
• Class
• Professor
• Subject

The following relationships exist between these entities:

• Class – Student: A student can attend many classes and each class can have many students. This is a many-to-many relationship.
• Class – Subject: A class is about a single subject, and a subject can have many classes. This is a one-to-many relationship.
• Class – Professor: A class can have only one professor, but a professor can teach more than one class (a one-to-many relationship).
• Professor – Subject: A professor can teach more than one subject, and a subject can be taught by more than one professor (a many-to-many relationship).

Tip: Want to avoid a bunch of warnings? When working with a conceptual data model using a logical diagram, allow the entities to have no attributes and identifiers, as shown below:

Expanding the Conceptual Data Model into a Logical Data Model

Our next step is to expand the conceptual data model into a logical model. You can achieve that by copying the conceptual diagram into a new logical diagram and then adding the details (like attributes, identifiers, etc.), as shown in this image:

We are keeping this model as simple as possible in order to focus on the differences between the model types and how you can easily implement them using Vertabelo. You can find a great example of a full learning/school system model in the article A Language School Database Model, available on this blog.

Creating a Physical Data Model from the Logical Model

Next, you need to create a physical diagram using the logical model as the source. To do this, just right-click on the logical diagram name and then click on the option Generate Physical Model:

You will have to enter a name for the diagram and select a target database engine, as shown below:

Then you will have your physical diagram! Vertabelo will automatically create any intermediate tables to handle any many-to-many relationships in the source diagram. You just need to adapt the names to your naming conventions:

Generating a Database Creation Script from a Physical Data Model

Once we have the physical model ready, we can apply it to a database using Vertabelo’s Generate SQL Script feature, which is available on all physical diagrams. Just click Generate SQL Script in the action bar:

Finally, select the objects you want to include in the script and click Generate. The script generation process will create the SQL file, which you can save to your Vertabelo drive and/or download to your computer:

What’s Next with Data Models?

Now that you know the difference between conceptual, logical, and physical data models – and how to use the Vertabelo Database Modeler to create them – you can start designing your own! In the comments section, share your thoughts on data modeling. And feel free to ask any additional questions or share your database model doubts.