What’s the difference between a logical data model and a physical data model?
Logical data models and physical data models are two important steps in data design. Data modeling is the process of creating a visual representation or a blueprint that helps different stakeholders generate a unified view of the organization's data. It begins with conceptual data modeling, where you create a high-level, abstract representation of your data entities, attributes, and relationships with inputs from business users.
The logical data model is a more refined version of the conceptual model. It diagrammatically represents data constraints, entity names, and relationships for implementation in a platform-independent way. The physical data model further refines the logical data model for implementation over a specific database technology. Logical data models and physical data models define the structure, organization, and rules of data to support efficient storage, retrieval, and manipulation.
Representation: logical data model vs. physical data model
Both logical data models and physical data models are tools that ensure business requirements are represented accurately on a physical database. They provide different levels of technical detail to support database design while maintaining the business perspective.
Representation of a logical data model
With a logical data model, business analysts and data architects can visualize operational or transactional processes in an entity relationship diagram. Logical data models define how data objects operate and transact in ways that business stakeholders understand. As such, they’re designed independently from the actual database that they’re deployed on later.
The following diagram shows an example of a logical data model for a sports ticketing system.
Each table describes the data entities and their respective attributes in familiar business terms. For example, the entity person contains full_name and last_name as attributes. For all entities, you designate a primary key (PK) to differentiate the attributes in each row. Some entities contain foreign keys (FK) to indicate their relationship to another entity in one-to-many relationships.
Representation of a physical data model
Physical data models provide in-depth details that help database administrators and developers implement the business logic on a physical database. These models offer additional attributes not specified in a logical data model, such as triggers, stored procedures, and data types. Because they map the data elements to an actual database, physical data models must adhere to platform-specific restrictions, such as naming conventions and usage of reserved words.
The following diagram shows an example of the physical data model for the same sports ticketing system.
In the physical data model, you state the data types for all stored data objects. You also revise the entity and attribute names to use platform-supported formats.
How to design: logical data model vs. physical data model
Designing both logical data models and physical data models requires steps that enable a seamless transition from business requirements to practical database implementations. The logical data model is an extension of the conceptual data model of a specific business process. The physical data model further refines the logical data model for database design.
Creating a logical data model
Follow these steps to build a logical data model:
- Determine all of the entities required and their respective attributes.
- Choose the appropriate PKs as unique identifiers for the groups of attributes.
- Normalize and denormalize the data model according to operational requirements.
- Establish the relationships among different business entities in the data model.
- Validate the data entities and their relationships to represent the business logic accurately.
You define the relationships between separate entities. Some entities are directly associated with each other, and others might be linked through a common entity. Usually, you consult with the respective stakeholders to ensure that the entities are connected correctly according to business requirements. You can also duplicate some entities and strategically limit others to a single instance to improve querying efficiency and minimize storage space.
Creating a physical data model
Follow these steps to design a physical data model:
- Convert the local data model to fit a chosen database provider's platform.
- Map data entities into their respective tables.
- Map and create PKs and FKs in the database tables as required.
- Verify that the database structure is normalized appropriately to remove redundant data and improve data integrity.
- Add relevant database constraints, rules, partitions, and programmatical features to support application development.
- Compare the physical data model and logical data model to ensure that the business requirements are translated correctly.
In some cases, one entity is divided into multiple tables. Each table contains several columns that store information specified by the logical data model's attributes. In a physical data model, columns are differentiated by their data types, such as integers, varchar, and Boolean.
Key differences: logical data model vs. physical data model
Despite being part of the data modeling process, logical data models and physical data models are distinguished by different characteristics.
Objective
With logical data models, you can visualize process workflows in a technically structured manner. You can understand the relationships between various business systems.
On the other hand, a physical data model describes how data is organized in actual database tables. You get a top-down view of how an application stores and accesses real-world data.
Creators
Data architects and business analysts typically create logical data models. Developers and database administrators are responsible for implementing physical data models.
Complexity
Logical data models are simpler because they define the interconnection of business data objects. Each data object consists of data entities and their associated attributes.
Physical data models are more complex. You organize data elements in a physical data model with tables, columns, and indexes and meet stringent platform constraints. Data structures in a physical data model are bound by a more rigid relationship, such as cardinality and nullability.
When to use
You use logical data modeling to visualize information flow in enterprise systems. Analysts, managers, and business users can understand how a particular system works and the applicable business concepts.
Application developers use physical data models to plan and optimize data storage when they build applications for production usage. Physical data models are the blueprint for storing data in a relational database.
Summary of differences: logical data model vs. physical data model
| Logical data model |
Physical data model |
|
| Platform-dependent database |
No. |
Yes. |
| Data structure |
Entities, attributes, PKs, and FKs. |
Database tables, rows, PKs, FKs, and data types. |
| Programmatical features |
No. |
Triggers and stored procedures. |
| Objective |
Visualize business logic with data structures. |
Organize data structure for database design. |
| Creators |
Business analysts and data architects. |
Software developers, programmers, and database administrators. |
| Complexity |
Simple. |
Complex. |
| When to use |
To understand enterprise systems and business rules. |
To plan, implement, and optimize data storage when you’re developing applications. |
How can AWS help with your logical data models and physical data models?
Amazon Web Services (AWS) databases include over 15 database engines to support diverse data models. For example, you can use Amazon Relational Database Service (Amazon RDS) to implement logical relational data models and Amazon Neptune to implement logical graph data models.
You can also use AWS Amplify DataStore for fast and easy data modeling to build mobile and web applications. It has a visual and code-based interface to define your data model with relationships, which accelerates your application development.
Here are ways you can perform data modeling on AWS:
- With Amazon RDS, you can build and scale databases with Amazon Aurora MySQL-Compatible Edition, Amazon Aurora PostgreSQL-Compatible Edition, SQL server, and other popular database engines
- With Neptune, you can build high-availability graph applications that can handle more than 100,000 queries a second
- Amplify Datastore provides client libraries that you can use to easily query, update, sort, or filter data on the cloud
Get started with logical data models and physical data models on AWS by creating an account today.
