Recently updated on July 17th, 2023 at 06:50 am
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.
- 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.
|Parameters of Comparison
|Logical Database Model
|Physical Database Model
|It describes data.
|It showcases data.
|Data architects and Business analysts
|Database Administrators and Developers
|It is simpler.
|It is Complex.
|Creates technical structures and technical map of rules.
|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.
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 ♥️
Sandeep Bhandari holds a Bachelor of Engineering in Computers from Thapar University (2006). He has 20 years of experience in the technology field. He has a keen interest in various technical fields, including database systems, computer networks, and programming. You can read more about him on his bio page.