A data model is a visual representation of a database structure. Data modeling can help at all stages of the database design process.
What are the Types of Data Model?
Every stakeholder in a project can be involved in data modeling, from non-technical business users to database developers.
There are different types of data models for each of the different audiences involved. These models include:
Conceptual data model
This model focuses on real-world entities, such as customers, employees or products. Conceptual models don’t go into much detail about the entity-relationship structure of the database. The resulting model is usually easy to understand, even for a non-technical audience.
Physical data model
Physical data models describe the database as it exists, including table structure and relationships. Physical models act as a kind of blueprint for developers, and they can form part of the technical documentation of the data project.
Logical data model
The logical model is an intermediate step between the conceptual and physical, which imagines the real-life entities as a relational database. This model includes some important technical details, such as the attributes associated with each data entity.
A typical database development project might start with a conceptual data model that’s designed in collaboration with business stakeholders and data architects. This evolves into a logical data model that helps data engineers to start building the required data infrastructure.
After that, a physical data model will guide the implementation of the database. It can also serve as a reference when running analytics, developing APIs, or performing data integration.
How is Data Modeling Performed?
Data modeling always starts with a real-world data requirement. The model refers to something that exists outside of the database, whether it’s a physical entity or something purely digital. The goal is to capture these real-world entities in relational data structures.
The process for building a data model goes like this:
1. Identify data entities
What things will the database be describing? For example, an e-commerce database might refer to several real-life entities, including customers, products, and sales records.
2. Define the attributes of each entity
Each data entity contains several attributes. For example, customers have a name, address, and date of the last order. Products have a product name, product code, and price. Sales records will have a date, amount, and a customer identifier.
3. Define relationships
In a model, relations are either one-to-one or one-to-many. For example, each customer might be connected to one salesperson and multiple sales records.
4. Create foreign keys
Additional reference tables are required when modeling a many-to-many relationship. These reference tables contain foreign keys that are used to manage complex relationships between data tables.
5. Normalize the model
The initial model may contain some inconsistencies, redundancies and dependencies that might impact the efficiency of the resulting database. Normalization removes these issues and produces the most efficient possible data model.
6. Sign-off from stakeholders
Modeling is often a collaborative process, and the data model forms the basis of the agreement between different departments. All interested parties should sign off on the data model before any development commences.
This process can become more complex when working with large data structures or planning physical data models. The principle remains the same in all cases, however: identity what’s being modeled, create a representational model, identify the relationships, and agree on the final model.
How are Data Models Implemented?
Conceptual, logical, and physical data models are just documents, like a blueprint for a house. Like a house, they must be built before they can be used.
Implementing a data model typically follows these steps:
1. Modeling iterations
The model may pass through several iterations, from conceptual to logical. As it passes through each iteration, it will become increasingly specific and technical.
2. Database construction
Data engineers, working in conjunction with data architects, will set about building the database specified in the physical data model. They can do this with SQL queries or using a tool like Visual Studio. If the model is not feasible or can be improved, the engineer will return to the project owners for approval.
3. Deployment and maintenance
The database is made available to the relevant business unit. Then it is tested to ensure it’s providing the required results. If the database is working, the data model is considered a success.