Logical vs Physical Database Model

Logical and physical database models are required in order to visually present the database that has been proposed for a certain business requirement. The models help in showing the association of business requirements and the database objects. This is necessary in order to gather all requirements of the database accurately and completely. Data modeling is the link between the system requirements and business needs. There are two data models, logical and physical.

Logical Database Model

Logical database modeling is required for compiling business requirements and representing the requirements as a model. It is mainly associated with the gathering of business needs rather than the database design. The information that needs to be gathered is about organizational units, business entities, and business processes.

Once the information is compiled, reports and diagrams are made, including these:

ERD–Entity relationship diagram shows the relationship between different categories of data and shows the different categories of data required for the development of a database.
Business process diagram–It shows the activities of individuals within the company. It shows how the data moves within the organization based on which application interface can be designed.
Feedback documentation by users.

Logical database models basically determine if all the requirements of the business have been gathered. It is reviewed by developers, management, and finally the end users to see if more information needs to be gathered before physical modeling starts.

Physical Database Model
Physical database modeling deals with designing the actual database based on the requirements gathered during logical database modeling. All the information gathered is converted into relational models and business models. During physical modeling, objects are defined at a level called a schema level. A schema is considered a group of objects which are related to each other in a database.
Tables and columns are made according to the information provided during logical modeling. Primary keys, unique keys, and foreign keys are defined in order to provide constraints. Indexes and snapshots are defined. Data can be summarized, and users are provided with an alternative perspective once the tables have been created.

Physical database modeling depends upon the software already being used in the organization. It is software specific. Physical modeling includes:

Server model diagram–It includes tables and columns and different relationships that exist within a database.
Database design documentation.
Feedback documentation of users.

Summary:

1.Logical database modeling is mainly for gathering information about business needs and does not involve designing a database; whereas physical database modeling is mainly required for actual designing of the database.
2.Logical database modeling does not include indexes and constraints; the logical database model for an application can be used across various database software and implementations; whereas physical database modeling is software and hardware specific and has indexes and constraints.
3.Logical database modeling includes; ERD, business process diagrams, and user feedback documentation; whereas physical database modeling includes; server model diagram, database design documentation, and user feedback documentation.

Sharing is caring!