Data Model Types: An Explanation with Examples
Data modeling is an essential part of designing a database. If you want to achieve the best outcome, make sure to utilize the available data models. Read on to find out more.
Every relational database has clearly defined objects and relationships among these objects. Together, they comprise the data model.
This article presents the concept of data modeling. First, we’ll go over data modeling and the steps of its process. Then we’ll jump into the various types of data models. You’ll see examples of conceptual, logical, and physical data models. I’ll also mention some of the more specific data models.
Let’s get started!
About Data Modeling
Relational databases organize data into tables that have connections among them. But before creating a physical database, you should model your data. Data models help visualize data and group it logically. Below are the three data models we’ll be focusing on in this article:
The base is the conceptual data model. What follows are the logical and physical data models. We’ll find out more about these data models in the following sections.
Data modeling is a vast subject. It is essential to the database design process. So make sure to check out our other articles on data modeling, such as What is Data Modeling, The Benefits of Data Modeling, and Why Do You Need Data Modeling. And if you still ask why we need data modeling processes and diagrams, read this article to learn about common database design errors that could be avoided by following the data modeling process.
The Data Modeling Process
There are several steps to be followed during the data modeling process. Let’s go through them one by one.
Step 1. Identifying entities
This step is a part of conceptual data modeling. Here, we decide on data groups according to the business rules. For example, when visualizing a grocery shop database, we would have entities such as Products, Orders, and Customers, as shown below:
Step 2. Identifying connections between entities
This step is part of conceptual data modeling. Here, we decide on the relationships (i.e. connections) between entities. For example, each customer would have one or more orders, and each order would have one or more products. We can see this in the image below.
Step 3. Identifying entities’ attributes
This step is part of logical data modeling. Each entity is assigned its attributes; this becomes the base for the physical data model. For example, each order would have an order ID, a customer who placed the order (customer_id), and products ordered:
Step 4. Deciding attributes’ specific data types
This step is part of physical data modeling. Here, we assign database-specific data types to the attributes of each entity. For example, an order_id would be an INTEGER and a customer name would be VARCHAR, as shown below.
Step 5. Identifying many-to-many relationships and implementing junction tables
This step is also part of the physical data modeling. Here, we create an additional table that stores many-to-many relationship data. For example, each order can have one or more products, and at the same time, each product can be ordered zero or more times.
Step 6. Creating database constraints, indices, triggers, and other database-specific objects
This step is part of physical data modeling. Here, we focus on implementing database-specific features. For example, let’s mark the primary keys and foreign keys (if needed) for each table:
Vertabelo lets you create an SQL script from the physical data model; when you complete the data modeling process, you can create your physical database in no time by executing the Vertabelo-provided SQL script.
Data modeling is part of database modeling. Check out this article to get a different perspective on the database modeling process as a whole.
Common Data Models
You now know the basics of the data modeling process. Let’s see how you might use it in practice.
Imagine that the local zoo hired you to design their database. We’ll create conceptual, logical, and physical data models to complete the entire database design process.
Conceptual Data Model
The conceptual data model focuses on identifying entities and relationships among them. We take into consideration business data, business rules, and business processes.
This data model is a strictly abstract representation of data. Its components include:
- Entities representing groups of objects that share attributes (which are defined later, in the logical model).
- Relationships between
Conceptual data models are typically created by data architects to present a high-level data overview to business stakeholders.
First, let’s identify the entities.
- Zoo_Employee stores data about the employees of the zoo.
- Zoo_Animal stores data about the animals living in the zoo.
- Animal_Species stores data on the animal species present in the zoo.
- Animal_Food_Type stores the types of food eaten by the zoo’s animals.
- Food_Provider stores data about companies or organizations that provide food types.
Now let’s discuss the relationships among the entities.
- Each animal has one caretaker, who is an employee of the zoo.
- Each employee can be a caretaker of zero or more
- Each animal has a species and eats a specific food type.
- Each food type is provided by one or more food providers, and each food provider can provide one or more food types.
This is the conceptual model to represent this data:
Next, let’s move on to the logical data model.
Logical Data Model
A logical data model dives deep into the data structure, assigns attributes to each entity, and specifies the database implementation details.
This data model is a base for the physical data model. The only difference is that logical data models are not database-specific (as opposed to physical data models, which are designed for one database management system like Oracle or MySQL).
We can create the logical data model in Vertabelo. Notice how many more details there are:
In addition to the attribute names, we have general data types (i.e. integer or varchar) and indicators for mandatory or non-nullable columns (M) and primary identifier fields (PI). PI fields will become primary keys in the physical data model.
This data model is still database-agnostic. The attributes’ data types are abstract, but Vertabelo converts them into database-specific data types when generating a physical data model.
Physical Data Model
The physical data model includes all database-specific features, such as data types, database indices, triggers, constraints, and more.
This data model is directly related to the database, as we can generate the database creation script solely based on this data model. It includes primary and foreign keys, column and value constraints, and other database-specific features.
Let’s generate a physical data model from our logical data model in Vertabelo.
This data model is database-specific. Here, we’re using PostgreSQL. To learn even more about the conceptual, logical, and physical data models, read this article.
Now that we’ve learned about the fundamental data models, let’s look at other available data models.
Other Data Model Examples
There are many different data models. The Unified Modeling Language (UML) offers various models used in software engineering. Some of them, such as a class diagram, are helpful in data modeling. Let’s look at some other useful data models.
Dimensional Data Model
Dimensional data models are used to implement data warehousing systems. These data models are handy in facilitating the analysis and retrieval of data.
The elements of a dimensional data model include:
- Facts, i.e. business processes whose information can be retrieved.
- Dimension, e. details for each fact. These usually answer the questions of who, where, and what.
For example, if we consider the feeding of an animal business process to be a fact, then the possible dimensions include the caretaker dimension, food type dimension, and feeding time dimension.
Object-Oriented Data Model
An object-oriented data model helps us more easily relate complex real-world objects. The elements of this model include:
- Class, e. a template for object creation.
- Object, e. an instance of a class.
- Attributes that characterize each object.
- Methods that describe the behavior of objects.
Below we have a sample object-oriented data model:
This data model provides more information on the specificities of each object/entity,
Entity-Relationship Data Model
The entity-relationship data model falls under the category of conceptual data models. It consists of entities, their attributes, and any relationships among entities.
Conceptual data models are all about the correct perception of data.
Try Your Hand at Different Data Model Types!
Any database design process begins with visualizing the data using various data modeling tools and diagrams. We usually use a top-down approach, starting with a general overview of the available data (conceptual models) and then drilling down to more and more details (logical and physical models).
Following this approach, the first step is to create a conceptual data model. It helps us initially organize the data and decide on the objects/entities and relationships among them. Next comes a logical data model that provides more details on the data structure, such as the attributes of each entity. At last, we convert a logical data model to a physical data model. A physical data model is an exact blueprint of your database.
With that knowledge, you’re ready to design your own database.