Difference Between Logical and Physical Data Model

Before discussing the difference between logical and physical data model, we must know what a data model is. A data model is a representation that describes the data and the relationships among them for a certain process. A data model is an essential component used during database design. Logical data model is a very abstract and high level view of data where entities, relationships and keys are identified. It is independent of the database management system (DBMS). A physical data model is derived from the logical data model where it shows how tables and columns are structured the real physical database. Physical data model is hence dependent on the database management system used.

What is Logical Data Model?

A logical data model describes the data and the relationships in detail at a very high level. This does not include how data is represented physically in the database , but describes at a very abstract level. It basically includes the entities and the relationships among them along with attributes of each entity.

The logical data model includes primary keys of each entity and also the foreign keys as well. When creating a logical data model first entities and their relationships are identified with the keys. Then attributes of each entity are identified. After that many to many relationships are resolved and normalization is done. A logical data model is independent of the database management system as it does not describe the physical structure of the real database. When designing a logical data model non-formal long names can be used for entities and attributes.

What is Physical Data Model?

A physical data model describes how data is really residing in the database. It includes the specification of all tables and the columns inside them. The table specification includes details such as the table name, number of column s and column specification includes column name and data type. The physical data model also contains the primary keys of each table and also it shows the relationship between tables using foreign keys. Moreover, physical data model contains constraints applied to data and components such as triggers and stored procedures.

The physical data model depends on the database management system used. So the physical data model for MySQL would be different from a data model drawn for Oracle. When creating the physical data model out of logical data model, first entities are converted into tables. Then relationships ae converted to foreign key constraints. After that attributes are converted to columns of each table.

What is the difference between Logical and Physical Data Model?

• A physical data model describes the physical structure of the database. A logical data model is a high level one that does not describe the physical structure of the database.
• Physical data model is dependent of the database management system used. However, the logical data model is independent of the database management system used.
• Logical data model includes entities, attributes, relationships and keys. Physical data model includes tables, columns, data types, primary and foreign key constraints, triggers and stored procedures.
• In logical data model, long non-formal names are used for entities and attributes. However, in physical data, abbreviated formal names are used for table names and column names.
• The logical data model is first derived from the description. After that only the physical data model is derived.
• The logical data model is normalized to fourth normal form. The physical database model will be deformalized if necessary to meet the requirements.
Summary:

Logical vs Physical Data Model

The logical data model is a high level data model that describes the entities and relationships among data. It also includes attributes and keys of each entity. This is independent of the database management system used. On the other hand, physical data model is derived after the logical data model and it includes the structure of the database including the specification of tables, columns and key constraints. This model is different according to the database management system used.