Different Data Models in Data Warehouse

There are three types of data modelling used to construct a data warehouse, namely:

1. Conceptual Data Model
2. Logical Data Model
3. Physical Data Model

Before going in depth, let us consider some importatnt objects created while designing a database:
Entity Names, Entity relationships, Attributes, Primary and Foriegn keys, Table names, Column names and Data types.

The below chart explains about the difference between the data models based on the database objects:



Conceptual Data Model:

Conceptual data model describes about highest level of relationships between entities. It includes only the entities names and their relationships. No table names, primary keys, foreign keys and column names are referred in this model.

Logical Data Model:

Logical data model provides more information on database structure, it include entities and their relationships, Primary and forign keys and provide more possibilities of handling normalization at earlier stage.



Physical Data Model:

A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Denormalization occurs based on user requirements in this stage.

We can see that the complexity increases from conceptual to logical to physical. This is why we always first start with the conceptual data model (so we understand at high level what are the different entities in our data and how they relate to one another), then move on to the logical data model (so we understand the details of our data without worrying about how they will actually implemented), and finally the physical data model (so we know exactly how to implement our data model in the database of choice). In a data warehousing project, sometimes the conceptual data model and the logical data model are considered as a single deliverable.