Demystifying Conceptual, Logical and Physical Data Models

Introduction

The Question (without a question mark?): Differentiate between Conceptual, Logical and Physical Data Models.

This is one of a number of data concepts that sort of stumped me for much longer than expected. Reason: every time I tried to understand the terms better, I would get more confused as it seemed to depend on the source of the answer to my question.

So, this time I set off to unearth the mystery of this question. However, I had my own point of view all along, which was not very different from what I found out (as I lay out below). Only that this time, I am more confident with my point of view.


My Point of View (now confirmed)

First, a data model is simply a representation of how data is organized and related in an information system.

A data model provides the definition and structure of data to be used in an information system based on business requirements and rules. Ultimately, it helps in directing the development of effective information systems.

Now that we understand Data Model, let’s understand the three levels.

Conceptual, Logical and Physical Data Models represent the hierarchical or progressive levels of details of abstraction of data models. My long-standing point of view has now been confirmed by the following findings.


Conceptual Data Model

A conceptual model is aimed at senior management, and it represents core business entities and the relationships between them.

In other words, a conceptual model describes the business requirements in business terms and natural language that is easily understood by the lay-person. For instance you might have entities such as student, course, and teacher. A conceptual model would define each of them precisely in plain language - the sort of description we see in System Envisioning Documents. A conceptual model would also help to define how the entities relate to each other at a high level.

It is not uncommon to have different viewpoints about conceptual model as seen by various stakeholders. For instance, there may be an argument on whether to consider a a teacher as part-time or full-time. The solution lies in clarity of system-specific definitions rather than relying on general definitions.


Logical Data Model

A logical model bases on the conceptual model for an in-depth analysis of the business and it adds the attributes for the entities. It is technology-neutral, meaning no particular RDBMS has been specified for use.

The logical data model implements the conceptual model using tables (entities), fields (attributes) and relationships. Here you would decide which entities you want to implement in your database. You also consider things like constraints, normalization, indexes and performance considerations.

You can get the logical model using the database-agnostic entity-relationship diagram (ERD).


Physical Data Model

The physical data model is often similar or identical to the LDM but can be different based on the technical platform.

It helps to design the actual database based on the requirements gathered during the logical data design.

You can get the physical model using the database-specific DDL SQL statements or the Data Model definition in your application.


Conclusion

The three levels of Data Models are in descending order of abstraction. Conceptual model is the most abstract and the Physical data model the least abstract or most concrete.

Conceptual Data Model is database-agnostic, bird’s eye, high level insights - from the Executive’s perspective.

Logical Data Model is database-agnostic, worm’s eye, low-level, detailed blueprint, description of entities that are defined and how they are related to each other and what kind of data is to be stored. This model determines if all the requirements of the business have been gathered.

Physical Data Model is database-specific implementation of the Logical model, deals with how the data will be stored in the database using suitable DBMS and this is generally handled by database administrators and developers in the application code.

At this point, I believe it is much clearer and easier to understand the three levels of Data Model.