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 table format. There are various types of database models like Network, Object, Document, etc. Logical and Physical Database Models are also a part of it.
Logical Database Model vs Physical Database Model
The main difference between the Logical and Physical Database Model is that the Logical Database is used for collecting information about a business and what it needs. While a Physical database Model is used for designing the database. A logical Database is software, and it can be implemented in any kind of database software. Whereas, Physical database is a specific kind of software and hardware and can be used for one particular purpose.
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 usually represented via diagrams. Once the logical model is approved, it can become the basis to form a physical database model. It tries to explain the data through its model but 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. Physical Database Model will consist of all the artifacts which are required to create relationships between tables or achieve goals like definitions, linking tables, partitioned tables or clusters, indexes, and constraint.
Comparison Table Between Logical and Physical Database Model
| 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 the business needs. It doesn’t focus on database design. The information that the logical database Model gathers are 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 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 support 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 has 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 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 Physical Model is possible based on the requirements. Developers and Database administrators develop Physical Schema. It needs DBMS and RDBMS systems for developing 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. Physical Database Model showcases data to be implemented.
- Logical Database models are created by Business analysts and Data architects. 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 complex than the Logical Database model.
- The logical Database Model creates technical structures and a technical map of rules. Physical Database Model objective is to implement Database.
- Logical Database Model modeling includes Business Process Diagrams, ERD, and User Feedback documentation. Physical Database Model modeling includes Database design server model diagram, documentation, etc.
Conclusion
Both the Logical and Physical database models are used for presenting the database visually. Both are required for business purposes, and they are known for completing the requirement of the business. Both form an important part of the database management system. Logical is more concerned with defining the data and not implementing it. While Physical is more concerned with designing data and implementing it also.
Physical Model is derived from Logical model. Logical can be developed independently. Whereas the physical database needs DBMS and RDBMS systems for developing it. The logical database can be applied in any kind of database as it is software. Whereas the physical is hardware and software specifications and has constraints and indexes.
References
- https://dl.acm.org/doi/abs/10.5555/172224
- https://escholarship.org/content/qt5bw2m3z5/qt5bw2m3z5.pdf#page=257