The data model is an architectural representation of data structures and the relative constraints that capture real-world entities. Data models document and describe how the data is related and used in business processes and help in building effective information systems or applications. For example, in a Business intelligence solution, a data model specifies which data types the users can use in their analytics.
There are significant differences between the logical data model approach and the physical data model approach to structured data. The first is a logical representation that focuses on what data is, and how different data elements are related to each other and it does not take into consideration how it would be implemented physically on the other hand the physical implementation represents data at the hardware level detailing how the data would be stored, where it would be stored, how it would be accessed and retrieved.
A logical data model is a type of data model that proclaims a detailed and organized plan that describes the data items as well as their relationships. However, ERD includes all entities, where an entity is a specific object from the real world (concerning business) and the connections between them. These entities have described attributes as characteristics of the entities.
As explicit, logical data models integrate what is required in application development business needs and high-quality structures in visuals. These models are designed and developed by business analysts and data architects. They identify correspondent business processes and uncover the business needs for developing the model that will serve the purpose of business objectives in a specific company. Also, they create a technical map of norms and patterns based on the nature of the work that is to be accomplished.
Characteristics of logical data models:
Technology-agnostic: The use of logical data models allows models to be highly portable across different DBMSs since they are independent of the latter.
High-level: By imparting the conceptual view of the structure, logical data models are appropriate to be used for getting an outline of the data/application.
User-oriented: Logical data models are specifically created to be comprehensible to the relevant stakeholders including business analysts as well as data architects when it comes to data requirements.
Data entity focus: Consequently, the logical data models concentrate on data entities, attributes, and the relations between them rather than concentrating on the table and its columns.
A physical data model indicates how the data model will be developed in the context of the database. Many of them specify the table structures, column name, data type’s column number and constraint, primary key and foreign key with indexes to the column of the related table, and the relation between the tables, store, procedures, and views. In most cases, decision-making concerning actual physical data model generation is usually a task assigned to the database administrator and developers. Many information systems and standard software programs base their operations on interaction with physical databases. Physical data models have to be properly planned and executed. One can find it difficult to alter physical data models as soon as the data from the existing application has been inputted into databases.
Key characteristics of physical data models:
Technology-specific: Physical data models are constructed specifically for a particular DBMS and incorporate the platform’s rules such as naming standards, and are unlikely to use words that have a meaning to the DBMS.
Detailed: Physical data models therefore provide a comprehensive depiction of the structure to give the real implementation and fine-tuning of the database.
Developer-oriented: This physical data model is meant for the database administrators and the developers where all the necessary details to implement the database are provided.
Table and column focus: Physical data models mostly pay attention to defining the kinds of tables, columns and their data types, possible indexes, and other physical implementation aspects.
| 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. |
Logical data models are a representation of the organizational structures of an enterprise at a high level of abstraction while physical data models depict the physical storage of data in a very detailed manner hence a clear relationship exists between the logical and physical data models. The physical model is built from the resource provided by the logical model and as such the two models can be considered to be closely connected. The process typically involves the following steps:
Logical data modeling: Detailing the conceptual model by even defining data constraints or defining the names of the entities in such a way that is independent of the actual platform.
Physical data modeling: Enhancing the logical model for a specific database technology implementation and determining the data type and indexes as well as other implementation characteristics.
Both logical and physical data models offer benefits in the data modeling process:
Logical data model benefits:
Physical data model benefits:
As for the difference between logical and physical data models, one can conclude that they are two interrelated pieces that make up the data modeling stage. The first one is the Logical Data Model which defines all business requirements and offers the necessary bird eye view of the data structures to be implemented; the second is the Physical Data Model which involves all the implementation details that are required for the creation of database systems.