A Database Model is a kind of data model that tells us how the data can be organized, stored, and manipulated. It also helps to determine the logical structure of a database.
The most popular example of a Database model is the Relational Model, which consists of a table format. There are various types of database models like Networks, Objects, Documents, etc. Logical and Physical Database Models are also a part of it.
Key Takeaways
- Logical models focus on data structure, relationships, and constraints; physical models deal with storage, indexing, and performance optimization.
- A logical model is database-agnostic, whereas a physical model is tailored for a specific database management system.
- Logical models serve as a blueprint for designing databases; physical models are used for implementation and maintenance.
Logical Database Model vs Physical Database Model
The logical database model, in the stages of the database design process, concentrates on the data structure and relationships, while the physical model specifies how the data will be stored and accessed in the database. The physical database model can consist of tables to organize the data.
A logical database also called a Logical data Model, or Logical Schema, is a type of data model. It is most commonly used in the process of businesses. It is represented via diagrams.
Once the logical model is approved, it can become the basis for forming a physical database model. It tries to explain the data through its model but does not implement it. The logical Database model can also be designed independently without taking the help of DBMS.
The physical database Model is the model which is implemented in the Database Management System, i.e., DBMS. Here the model or data is represented via designs. It is derived from the Logical Database.
The physical Database Model will consist of all the artefacts which are required to create relationships between tables or achieve goals like definitions, linking tables, partitioned tables or clusters, indexes, and constraints.
Comparison Table
| Parameters of Comparison | Logical Database Model | Physical Database Model |
|---|---|---|
| Definition | It describes data. | It showcases data. |
| Created By | Data architects and Business analysts | Database Administrators and Developers |
| Simple/Complex | It is simpler. | It is Complex. |
| Objective | Creates technical structures and technical map of rules. | Implementing Database |
| Modeling | Business Process Diagrams, ERD, and User Feedback documentation. | Database design server model diagram, documentation etc. |
What is Logical Database Model?
The logical database Model is concerned with collecting information about business needs. It doesn’t focus on database design. The logical database Model gathers information about business entities, organizational units, and business processes.
It is mainly represented via diagrams. Its structures are abstract, which shows the domain of information. It is very much simpler than the physical database Model.
Sometimes Logical database model and the Domain model are used interchangeably because they are closely linked together. They both have the same goal like both are concerned with capturing the data or domain rather than the structure of the data.
The logical database Model was first introduced in the year 1975 by ANSI. At that time, no software existed which supported this logical schema. The Logical Model was hierarchical and networked.
After the Logical Database model, an object-oriented approach was implemented, which can describe data in terms of attributes, classes, and associations. The logical database can be made independently without using DBMS.
It is represented through platforms like DIV-2 Viewpoint and OV-7 View. The logical database can be applied in any kind of database as it is software. Usually, Logical Data models are created by Business analysts and Data Architects.
What is Physical Database Model?
Physical database Model or Physical Schema is normally used in data management to explain and showcase how data is represented and stored using DBMS.
Here the design is implemented to show the lifecycle of a project. It can also be reverse-engineered from a given database. Normally it is derived from the Logical database model. It has hardware and software specifications and constraints, and indexes.
There are seven types of databases available in the market like Informix, SQL Server, Sybase, DB2, Oracle, Postgres, and MySQL.Physical Schema is very specific, catering to the particular databases only in their implementation.
For example, SQL will only run in the Windows operating system by Microsft. This model is used to generate the schema. It also helps to model the database column keys, indexes, triggers, constraints, and other RDBMS features.
Modification in the Physical Model is possible based on the requirements. Developers and Database administrators develop Physical Schema. It needs DBMS and RDBMS systems to develop it. It is very much complicated if we compare it with the logical database model.
All the information which are collected in the physical schema is converted to business and relational models. Tables and columns are made in it according to information provided by the logical model.
Main Differences Between Logical and Physical Database Model
- The logical Database Model describes data and not how they are implemented. The physical Database Model showcases the data to be implemented.
- Logical Database models are created by Business analysts and Data architects. The physical Database Model is created by Database Administrators and Developers.
- The logical Database Model is simpler than the Physical Database Model. The physical Database Model is very much more complex than the Logical Database model.
- The logical Database Model creates technical structures and a technical map of rules. The physical Database Model objective is to implement a Database.
- Logical Database Model modelling includes Business Process Diagrams, ERD, and User Feedback documentation. Physical Database Model modelling includes Database design server model diagram, documentation, etc.
- https://dl.acm.org/doi/abs/10.5555/172224
- https://escholarship.org/content/qt5bw2m3z5/qt5bw2m3z5.pdf#page=257
I’ve put so much effort writing this blog post to provide value to you. It’ll be very helpful for me, if you consider sharing it on social media or with your friends/family. SHARING IS ♥️
Logical database models, with their business-focused approach, contrast with the database-agnostic, system-specific nature of physical database models. Great explanation in the article.
The comparison table provided a clear overview of the differences between logical and physical database models, addressing essential parameters such as complexity, objectives, and creators.
The article provides a comprehensive explanation of the logical and physical database models and their key differences, as well as real-world applications.
The article provided valuable insights and definitions of logical and physical models, their uses in database design, and the differences between the two, which is useful for professionals and learners.
The article’s detailed description of logical and physical database models, their purposes, and representation methods offers a comprehensive understanding of these essential database concepts.
This article is a fantastic resource for understanding the different types of database models such as Relational, Networks, and Objects, and the distinctions between logical and physical models.
I appreciate the in-depth comparison between the logical and physical database models and how they play unique roles in database design and implementation.
The article does a great job of highlighting the distinctions between logical and physical database models, from their objectives to their creation by different stakeholders like data architects and database administrators.
This article effectively explains the logical and physical database models, their conceptualizations, and real-world applications. It serves as an informative guide to these database concepts.
Logical models are crucial for understanding data structure and relationships, while physical models focus on storage and performance optimization. The article explained this difference very clearly.